THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Using SSIS Package Configurations

SQL Server Integration Services (SSIS) is a very powerful tool for creating ETL Packages. Part of what makes it so powerful is its ability to use package configurations. Package configurations allow you to externally store information that the package needs so that information can be easily changed without re-writing the package itself. You can store connection strings, variable values, package passwords, isolation levels, and much more. Basically, if you can set the value in SSIS, it can probably be stored in a package configuration. This makes modifying a package, such as when it moves from QA to production, or when a production server’s name changes, much easier. Package configurations come in several flavors and which one you use will depend on your individual needs. Also, each type stores data differently, so in some cases multiple values can be saved and in others you can store only a single value. Here is a quick breakdown of the types of package configurations.

· XML Configuration File – Multiple values can be stored in an XML file on disk

· Environment Variable – A single value can be stored in a Windows Environment variable

· Registry Entry – A single value can be stored in a registry key

· Parent Package Variable – The parent package can pass a variable to the package which contains the configuration value

· SQL Server – Multiple values can be stored in a table on an SQL Server

Each package can have more than one configuration and I personally like the environment variable/ SQL Server combo. This allows me to store most of my values in one place, the SQL Server, and still be able to dynamically change the location of my configuration server without needing to change the package. Here is how I set up my package configurations.

First, make sure that you have a connection set up for your SQL Server where you want to store package configuration details. Then, from your SSIS control flow, click the SSIS menu and choose Package Configurations. This opens the package configuration dialog box. From here, you must check Enable Package Configurations in order to do anything. Once you have enabled configurations, click Add and then choose Environment Variable from the Configuration Type drop down. Name it something like ConfigServer and click next. Browse to the Connection Managers folder, find your configuration server connection and select its connection string property, as shown below.

clip_image002

Click next, name the configuration and click finish. Next, go and create a Windows Environment variable with the same name you used in the package configuration and set its value to a valid SQL Server connection sting for your configuration server, something like:
Data Source=CSSRV04;Initial Catalog=msdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;

This package configuration will allow your SSIS package to find the SQL Server where the rest of your configuration will be stored.

Now add another package configuration, this time with a type of SQL Server. Specify all the connection details to the SQL Server on which you want to store your SSIS configuration, including the table to which you want the values written and click next. Now pick any and all values you want to store in SQL Server, name the configuration and click finish.

The next time your package runs, it will use the Environment Variable to find your SQL Server and the SQL Server to find the rest of your settings. These values can be changed at any time without the package requiring any modification.

Published Monday, September 21, 2009 10:55 AM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Carl Cook said:

Just wanted to share how I made a package that can go unchanged from dev to qa to prod without Package Configurations or environment variables.  One thing we did is make a share with the same name in every envirnment, then save files using connection manager connection string expressions like "\\\\"+@[System::MachineName] +"\\shareName\\fileName.txt".  

For the database connection string it is like "Data Source="+ (LOWER(@[System::MachineName]) == "prod_computername" ? "prod_DB" : LOWER(@[System::MachineName]) == "qa_computername" ? "qa_DB" : LOWER(@[System::MachineName]) == "dev_computername" ? "dev_DB" : "not found") + ";Initial Catalog=hcpweb 4 netscope;Provider=SQLOLEDB.1;Integrated Security=SSPI;".  Setup was easy and maintanence should be super low.

February 10, 2010 6:47 PM
 

Bruno Pimenta said:

Hi,

First of all thanks for a great post Eric.

Second I would like to ask for your help. I'm trying to use an environmental variable to pass the connection string to the SSIS packages but with no success. I want to be able to map the default connection and user for package execution. Here is an example of what I'm passing and does not work:

Provider=SQLNCLI.1;Data Source=DATA_SOURCE_NAME;Persist Security Info=True;Password=PASS;User ID=USER;Initial Catalog=CommonConfig;

If I use a default connection, with a windows account it works:

Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Commonconfig;

So the difference is instead of using the windows account and localhost I want to use a defined account and defined data source.

Can you please advise me on this one?

Thanks in advance,

Bruno Pimenta

March 1, 2010 7:29 AM
 

samthegreat said:

Hi Eric & Bruno,

Bruno! Did you got the answer in your email by Eric? Coz I have the same problem. I create environment variables and send the package config file using environment variable but I can't see my config file in n/w folder and if I used XML config. I am not able to run in Prod server or UAT?

Any advise Eric?

September 17, 2011 9:40 PM
 

Richa said:

Hi,

First of all thanks for a great post Eric.

Second I would like to ask for your help. I'm trying to use an environmental variable to pass the connection string to the SSIS packages but with no success. I want to be able to map the default connection and user for package execution. Here is an example of what I'm passing and does not work:

Provider=SQLNCLI.1;Data Source=DATA_SOURCE_NAME;Persist Security Info=True;Password=PASS;User ID=USER;Initial Catalog=CommonConfig;

If I use a default connection, with a windows account it works:

Provider=SQLNCLI.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Commonconfig;

So the difference is instead of using the windows account and localhost I want to use a defined account and defined data source.

Can you please advise me on this one?

Thanks in advance,

Richa

April 20, 2012 11:07 AM
 

dghjkl said:

Provider=SQLNCLI.1;Data Source=DATA_SOURCE_NAME;Persist Security Info=True;Password=PASS;User ID=USER;Initial Catalog=CommonConfig;

April 11, 2014 11:19 AM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement