<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Passing Parameters to SSIS package

Published on
10,912 Points
3,812 Views
1 Endorsement
Last Modified:
Approved
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>

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:
My Computer Property

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:
Package ConfigurationSelect Package variable
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
1
1 Comment

Expert Comment

by:sksamguru
Hi PatelAlpesh,

Could you please describe little more on environment variables part. what is difference between selecting from configuration type and configuration location is stored in environment variable as indirect? Please see my attachment.
env-variable.jpg
direct-Env-Variable.jpg
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month