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.


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

  • For sharing with others, I put more context to the answer above. First you need to create an Xml file which can assign the values to rhe package variables in it. Then open the SSIS solution, click on SSIS menu and select package configuration. Mark it enabled and choose XML file you had created before. Here is a very good link showing you how to create that XML file: jasonstrate.com/2011/01/…
    – Sky
    Aug 20 '12 at 7:03

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.