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.
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.