Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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