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!