Passing Parameters to SSIS package

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing.

I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common question of my colleagues has inspired me to write an article on Experts Exchange.

In short, parameters are passed to SSIS packages in five different ways.
Note: You can see all of these options from the SSIS => Package Configurations Menu.
 

1. XML Configuration File

An XML configuration file works the same as a normal .config file.
 

<DTSConfiguration>
                      	<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
                        		<ConfiguredValue>Data Source=[SERVERNAME];Initial Catalog=[DATABASENAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> 
                        	</Configuration>
                      </DTSConfiguration>
                      

Select all Open in new window

You can assign values to .config file key (app key) and assign the property to a local variable within your package. When the package executes, the values of the local variable are replaced with the .config file property (key) value.
 

2. Environment Variable

Another option is to use an environment variable, which needs to be configured through Advanced System Properties of My Computer. Illustration below:

3. Registry Entry

A third option, though somewhat risky, is to use a registry entry.  As with making any registry changes, if something goes wrong with registry, there is a chance the whole system could crash or go down.
 

4. Parent Package Variable

The fourth option is very useful when you are executing child packages from a master package and want to pass some values from the master package to the child package(s).

To use this method, you need to pass the name of a variable (of parent package) and assign that value to a local variable (of child package) within the local package.
 

5. SQL Server

The final option has dependency on SQL Server. If you need to store the variable value in SQL Server (i.e., persist the value) and remove some space from memory, then this option is the best way.

To do this you need to pass a connection string and table name where you want to save the variable's value as illustrated below:

Please let me know if you have any queries regarding the article or the workings of SSIS. It will be my pleasure to entertain them.

Thanks,

Alpesh