<

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

x

Passing Parameters to SSIS package

Published on
10,895 Points
3,795 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
Comment
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month