31 Days of SSIS
The last post in the 31 Days of SSIS talked about the use of environmental variables. As I mentioned in that post, environmental variables are great for values that are specific to individual environments
One problem with environmental variables that I mentioned is often the operating system needs to be rebooted after adding new environmental variables. In the real world SSIS packages will be deployed more often than you probably want to go around rebooting your servers. Due to this an alternative to environmental variables is required. The alternative we will be discussing today is SQL Server configurations.
Why SQL Server Configuration?
SQL Server configurations allow properties in SSIS packages to be set in a table and retrieved at run-time. When the package is designed, the items are selected through the configuration manager and inserted into the configuration tables.
Conceptually the properties set through environmental variables and SQL Server configurations are the same. Both are properties that are set at the package level that will be used for all executions of the package within that environment. Both need to be added to the package at design time.
The purposes and intent behind using each of these is a slightly different. These differences are due to where the configurations are located and how they are set. SQL Server configurations changes can be inserted into the configuration table; after which they are immediately available. As opposed to environmental variables which require the reboot.
SQL Server configurations are more secure, in my opinion. Since anyone logging onto a server can see environmental variables. To get to the SQL Server configuration you need have access to the database and the table. With transparent data encryption the configuration can be secured one step further.
These differences point help provide guidance on how each should be used. SQL Server configurations are a good fit for configuration changes that can change at deployments, between executions, or because the sky is blue. The typically are associated to only as single or a few packages. On the other hand, environmental variables are more appropriate for properties that change infrequently – such as during SQL Server upgrades or hardware migrations. They are also a good store connection strings for databases that are accessed through Windows authentication.
SQL Server Configuration Setup
At this point, there’s been enough talking about SQL Server configurations. Let’s dig back to the SSIS package from the Override Variables post and configure it to use SQL Server configurations. For the first couple steps I’m not going to guide you through them since they are either basic or are part of previous posts.
Start by creating a database for the SQL Server configurations to be stored in. I suggest naming that database SSISConfig, because it’s so obvious. Next add an environmental variable to your machine named SSISConfig with the connection string to the SSISConfig database. As I mentioned, long term values go in environmental variables; like the SSISConfig connections string. Lastly, add a connection to SSISConfig in the Override Variables SSIS package, found above. At this point, we are ready to begin setting up a SQL Server configuration.
First open the Package Configuration Organizer window from the main menu or right-click menu in the Control Flow. Once open, select Enable Package configurations. And then select the Add… button.
Once this is done, we need to setup the connection string to the SSISConfig database on the SSISConfig connection. Do this through the use of environmental variables and map the value to the connection string for the connection.
Now that the SSISConfig connection is setup, we need to start adding SQL Server configurations to the SSIS package. Start similarly to setting up the environmental variable by clecking the Add… button on the Package Configurations Organizer.
In the Package Configuration Wizard, choose the configuration Configuration type of SQL Server. Then change the Connection to SSISConfig, it will likely default to AdventureWorks due to the way the alphabet works, captain obvious.
The Configuration table list will be empty if this is your first time using SQL Server configurations. Click the New… button to create a configuration table. Typically I use the table suggested, minus the space in the name. Change Configuration table to the name of the table you created.
The next piece to add is the Configuration filter. This can be anything that you want it to be. Make sure it describes what you are adding to SQL Server configurations. It should be unique enough that it isn’t confused with configuration information. It should also be general enough that it can be shared between packages when it is applicable. For this example, set the filter to OverrideDates. With the filter set, click Next….
On the Select Properties to Export screen browse to the variables within the package in the properties tree view. Find the property for OverrideStartDate and OverrideEndDate. Expand both of these properties and select the Value property to add to the SQL Server configuration. The click on Next….
On that last page of the wizard you need to add a name to the SQL Server configuration. The name can be anything that you would like it to be. For simplicity sake, I usually give it the name of the SQL Server configuration filter.
Run the package and you will see that it executes similar to how it did previously. If you edit the values in the configuration table, you will get
SQL Server Configuration Wrap-Up
SQL Server configurations are my preferred method for configuration environment specific values for an SSIS package. They are easy to manage and can be monitored and secured without much effort.
There is an additional benefit that SQL Server configurations have over environmental variables. Once a configuration has been setup, bringing those configurations into an SSIS package doesn’t require the extra work to map the values. SQL Server configurations map to the XML path for the property and automatically know what properties to modify because of that.
One thing I didn’t point out, that is worth noting, is the data within configuration filters can be changed without having to edit a package. If you miss a property that needs to be set at run-time, you can put together the configuration path to the property and insert the property to the table. The next time the package runs, it will pick up the new property. This, of course, assumes that you already have a SQL Server configuration setup on the package.
Package Download
Related posts:
- 31 Days of SSIS – Environmental Variable(17/31)
- 31 Days of SSIS – One Variable to Rule Them All (5/31)
- 31 Days of SSIS – Folder Structures (4/31)
