Passing values to variables defined in SSIS package at run time

Question

I need to pass values to the variables in a SSIS package at run time. Obviously it is not practical to open BIDS and set a variable every time the package needs to be run. We run it on SQL Server 2008 R2.

What is the best way to do that?

For example we have 3 variables used in the package. 1 of them are populated in the packages on fly, the other 2 needs to be specified from begining:

Let's say we have these variables defined in the package:

• @Rec_Count (No need to set a value at run time - it will be populated by a dataflow automatically)

• @Fetch_Size (Should be set a value when the package is kicked off)

• @File_Path (Should be set a value when the package is kicked off)

How can we set the last two variables in run time by the user?

Thank you in advance.

Answer 1

You can have the values specified in a .dtsconfig file, or you can set them using the /SET option of dtexec.exec

Answer 2

Well there are many tools that you can use in integration services to do this. I would suggest using a Execute SQL task or an Execute Script task and map and then map the result set to the variable desired. This will give the ability to dynamically set the variable.