Feeds:
Posts
Comments

Posts Tagged ‘SSIS with parameter’

Open BIDS.

Create a new integration service project.

Go to Control Flow Tab and right click on window.

Then, go to variables

Add three varaibles namely input_1,input_2 and Name with data type int32,int32 and string respectively.

Add Parameter’s default values 10 to each input_1 and input_2 where as ‘amit’ to variable Name.

Add Execute Script Task to Control Flow

Click on Script Task/Edit, Go to General tab and add read only variables that we have defined above.

Click on Edit Script and write the code as provide below

Dim input1 As Integer = CType(Dts.Variables(“input_1”).Value, Integer)

Dim input2 As Integer = CType(Dts.Variables(“input_2”).Value, Integer)

MsgBox(“Hi “ & Dts.Variables(“Name”).Value.ToString() & Environment.NewLine _

& “Multiplication of two numbers provided is :” & (input1 * input2).ToString() & Environment.NewLine _

& “Bye”)

Execution with Default Parameters:

Go to Command Prompt and browse till C:\Program Files\Microsoft SQL Server\100\DTS\Binn

(Do check your Installation  Directory of Micrsoft SQL Server and Configure your Path accordingly)

and Then

DTExec.exe /f  “your_path_to_dtx_package”

In my case it will be

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx”

So, your command should look like below

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx”

And Run the above

Execution With  Parameters:

Now, after executing Package with default parameters give your own parameters and excute

C:\Program Files\Microsoft SQL Server\100\DTS\Binn>

DTExec.exe /f  “G:\w\Integration Services Project1\Integration Services Project1\Package.dtsx” /SET \Package.Variables[User::input_1].Properties[Value];”29″ /SET \Package.Variables[User::input_2].Properties[Value];”29″ /SET \Package.Variables[User::Name].Properties[Value];”Singh”

And Run the above

Read Full Post »

%d bloggers like this: