Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Alpesh Patel is working on Various Microsoft Products (Visual Studio 6.0/2005/2008, SQL Server 2005/2008, BI Tool etc)

Administrators

Browse by Tags · View All
SQL Server 9
SSIS 5
#SQLServer 4
#BI 3
BI 3
SSRS 3
SQL Server 2012 2
TSQL 2
Integration Service Catalog 1
Master Data Service 1

Archive · View All
September 2011 3
December 2012 2
October 2011 2
August 2012 1
July 2012 1
February 2012 1
November 2011 1
August 2011 1

Alpesh Patel's Blog

Passing Parameters to SSIS package

Aug 11 2011 12:38AM by Alpesh Patel   

This is my first ever Article; 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.


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>

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

Untitled

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 Configuration

Untitled_003

Select Package variable

Untitled_002

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

Tags: SSIS,


Alpesh Patel
36 · 5% · 1663
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Passing Parameters to SSIS package" rated 5 out of 5 by 2 readers
Passing Parameters to SSIS package , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]