<

Passing Parameters to SSIS package

Published on
10,760 Points
3,660 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

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month