Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Passing Values into an SSIS Package at Runtime from Outside the Package

SSIS provide several methods for passing values into your packages at runtime.  The benefit of doing so allows you to change the results of the package without having to even open the package in the development environment (BIDS). 

The typical way of doing this is to create variables inside your package that other SSIS tools can interact with from outside of a package.  The tools described in this post will be Configurations, SQL Agent Job, and DTEXEC.  There are other methods as well but these are the simplest.  So the first step is you must create a variable that will store whatever value you wish to pass into the package. 

Now let’s look at the methods for passing in values to that variable.

Configurations

There are many debates which type of configuration should be used, but whether you use Config files, Config Tables, or even Environment Variables it will give you the ability to make changes from outside of the package.  Configurations can be added to a package by:

1.       Right-clicking in the Control Flow of a package and selecting Package Configurations. 

2.       Next, click the checkbox to enable package configurations and then hit Add.

3.       Click Next past the Welcome screen of the wizard.

4.       Select the type of configuration you wish to use and a location for it then click Next(I’m not going to focus on each on in the post).

5.       Select the object to configure.  This can be anything inside of the package but for our example let’s say it is a variable value as shown below. 

6.       Click Next and name the configuration before you click Finish.

This will allow you to manage the value of this variable from a configuration file, table, or Environment Variables.  You simply edit the configuration to modify how the package will run.

SQL Agent Job

A variable value change can also occur inside a SQL Agent job.  If you create a SQL Agent job that runs your SSIS package then you are able to pass values into variables by using the Set Value tab of the job.  This is visible when you have selected the SQL Server Integration Services Package for the step type.  Below I show how you can replace the default value of a variable during the run of the package from this SQL Agent job. 

The recognized format for the Property Path must be similar to this: \package.variables[variablename].Value

Command Line

The last method I’ll mention in this post is using the command line prompt called DTEXEC.  By opening cmd.exe you can run package and even pass values into a package variable.  This is very similar to how the SQL Agent job performed the same task.  If you look below you will see the script uses dtexec to call the command line prompt for running SSIS package.  The /f indicates that the package is stored in a file system.  If the package is stored on the server then you indicates that with a /SQL.  Next is the location of the package that should be run.  In this care that location is C:\SSIS\Troubleshoot.dtsx.  Then finally the /set command  will allow you to change values that exist in the package.  In this example \package.variables[variablename].Value;"mynewvalue" is replacing the value in the variablename variable with the value mynewvalue.  One last thing I’ll note about the command line is that the V in Value but be capitalized for it to work.

dtexec /f C:\SSIS \Troubleshoot.dtsx /set \package.variables[variablename].Value;"mynewvalue"

 

I know there are other avenues for performing these same tasks so feel free to share your methods of changing package values from outside of the package with me!

Comments

Posted by Anil Maharjan on 16 March 2011

Hello Devin,

Firstly, thanks for sharing this post and also I want to add something regarding passing values dynamically to SSIS package. In one of my package, I used the following script and execute package along with passing variable dynamically or outside the SSIS from SSMS.

DECLARE @ssis_cmd VARCHAR(4000)

DECLARE  @Packagepath VARCHAR(50)

DECLARE  @FileName VARCHAR(50)

SET @Packagepath = 'C:\Test_Xp_cmdshell\Package.dtsx' -- SSIS package location

SET @FileName = 'D:\SSIS\File.txt' --Passing dynamic variable i.e 'file location' into ssis package.

SELECT @ssis_cmd = 'dtexec /F "' + @Packagepath + '"'

SELECT @ssis_cmd =

@ssis_cmd + ' /SET \Package.Variables[User::FileName].Properties[Value];"' + @FileName + '"'

EXEC master..xp_cmdshell @ssis_cmd

Regards,

Anil Maharjan

Posted by Christopher G.S. Johnson on 31 March 2011

Thanks for the article, Devin, and the follow-up, Anil.  I recently learned something regarding this topic from Andy Leonard at a SQL Saturday session in Columbia, SC.  In SQL 2008, whatever value is in the config file over-rides the value set in the command line.  In SQL 2005, the command line wins.  Hope that this helps.

Thanks...Chris

Posted by josh on 20 July 2011

I wonder if you can do this with an inputbox() prompt when simply running a package from within visual studio? I am trying to figure out how to prompt each time I hit the debug play button... Any ideas? :)

Posted by Srinivas-169874 on 30 March 2012

I have a SSIS package that is deployed on a server, Say Server A. This is a Production server on which we do not have a access. They have created a job say Job A. I have a development server, Server B on which i create another Job Job B which just runs the job(Job A) on Server A using sqlcmd.

Now my requirement is i need to load data giving parameters @StartDate, @EndDate to the SSIS package. How can i pass these values to the Package on Server A. I need to load data in chunks, say 6 months data at a time. I dont even have access to the configuration file on Server A.

Leave a Comment

Please register or log in to leave a comment.