Non-Standard uses for SSIS Package Configurations

by Joe Toscano, RDA

SQL Server Integrations Services Package Configurations: Much more than dynamic connections
Package Configurations in SSIS allow you to dynamically change object property values such as a connection’s ConnectString simply by changing a row in a SSIS Package Configuration table or an XML Configuration File. They make your SSIS solution much more flexible in many ways and while connection strings are tops on the package configuration hit list, this blog entry discusses additional ways one can take advantage of SSIS Package Configurations.

Why do we need Package Configurations?
Think about how this feature can be used. As your SSIS packages moves up the food chain from Development to Test and eventually to Production, you may need to change many package attributes along the way. Maybe while in TEST, your packages extracted sample data from a partial copy of the production data. Maybe while in TEST your package wrote to a TEST destination (SQL Instance), or FTP-ed files to a ftp development folder from an FTP TEST Site. Finally, maybe while in TEST your solution emailed a group of developers if there were any package failures. What if you thoroughly tested your SSIS solution in this environment and you are ready to move your solution into production?

Scary memories of DTS and package promotion / deployment
If you worked with SQL Server 2000’s DTS, one of the only ways to do deploy a package to a new server was while in the DTS Package Editor issue a Package à Save As and change the SQL Instance where the Package physically resides. Once this was done, in many cases you had to then manually change all of the connections to point to the correct product-mode sources and destinations. This was a painstakingly manual process. In many cases, many other package attributes needed to change in addition to sources and destinations. Enter SSIS Package Configurations. They allow you to modify rows in a Package Configuration Table or XML configuration file to achieve the same end-result.

How Package Configurations Work
While there are several types of Package Configurations in this example we have chosen to store our package configuration / dynamic values in a SQL Server table. Further, we have chosen to create a database called SSIS that contains our package configuration tables along with some other tables and stored procedures used by our auditing subsystem. SSIS allows you to define a Package Configuration Environment Variable, and we use this to store a connect string to the SQL Instance that houses this SSIS database. Our environment variable was called SSISPACKAGECONFIGURATIONS. We maintain multiple copies of the PackageConfiguration table each with different values to support the different environment. For example – we have one package configuration table that is used in my local development environment to point to my local SQL 2005 Developers Edition, while another was created for our shared test SQL Instance and yet another for our official production SQL Instance.

Both standard and non-standard Package Configuration Examples

SSIS package Configurations are easily created in Business Intelligence Development Studio. (SSIS -> Package Configurations)
Let’s look at our package configuration categories and provide a typical Business Intelligence Development Studio (BIDS) Solution that utilizes them:

1. Source and Destination Connections and directories
SSIS Package configurations can be used to hold the connection strings for our connections to our SSIS, Stage and SalesDW databases which are databases used by our solution. In this solution, we are extracting monthly sales data from a FTP Site, so we can dynamically store the directories used to support this operation along with the FTP security credentials.

a. SSIS, Stage and SalesDW OLEDB Connections
These represent standard OLEDB connections used by packages as either a source or destination. This may the most common use for package configurations. For these package configurations we choose to designate the ConnectString property of these connections making it the dynamic property we can easily change.

b. FTP Working Directory
Our solution uses an ftp .bat file and ftp scripts to drive the FTP get and put commands. We’ve found this gave us more flexibility than using the SSIS FTP task. This package variable designates the directory that houses the .bat and script files used by the FTP command. We had one location while our solution was in development, but in production our servers were clustered so we couldn’t reference a server’s local drive.

c. FTP Local Directory, FTP Remote Directory
Once connected to the FTP site you can change your local and remote directories using the lcd the cd commands. The local directory setting determined where files resided after they were gotten using the FTP mget command. As packages moved from development to test to production these did need to change so creating package variables whose values were package configuration items saved us on package maintenance.

d. SSIS Package Directory
Initially this was set to the ‘working directory’ while we were working with the SSIS Solution in the Business Intelligence Development Studio. (In our case, the working directory was a SourceSafe local working directory setting) Once the packages were deployed to a production server, this changed in the package configuration table present in the production server to the actual deployment path on the production server. (for our solution, we stored our package files in the file system as opposed to in SQL Server)

2. FTP Attributes

We’ve found that while in development mode we relied on a test FTP instance, but once our solution was deployed to production we were asked to use the official production site. All security credentials changed; therefore, storing these as package variables whose values were package configuration items saved us on package maintenance.

a. FTP Site Name, FTP Login, FTP Password
The FTP Security credentials were stored in package variables as package configuration items. This allows us to easily make changes without having to modify our SSIS Solution. Instead, we modified rows in the package configuration table.

b. FileToExtract
Our FTP site supported the use file wildcards (% matched any single character) and we were able designate the file to extract making the Year and Month (YYMM) portion of the file match any year and month. This way no matter what files were dropped to our FTP Site, our solution would auto-discover them and then extract and load them.

c. ParentTraceMode
We’ve found that while in development mode we actually wanted to the FTP results. What files did FTP get or what files did FTP move/rename. At first, this wasn’t possible because the FTP window appeared and disappeared too quickly since we were working with very small files. We decided to create a package variable called ParentTraceMode whose value is obtained from the calling parent using the Parent Package Configuration. If this value is set to 1, SSIS Precedence Constraints force the execution of a trace mode FTP command that simply does not provide the ending FTP quit statement. Sounds simple, but without the quit command the FTP window stays displayed for us to visually inspect. I found this trace flag also useful to direct the flow of the package to enable other tasks such as Script Tasks the display useful information via message boxes. Also, our FTP processing normally cleans up after itself by removing the FTP Script files that were just used. Good thing because there’s some sensitive security credentials in this file! With trace mode enable these script files remain in the FTP Working Directory for inspection.

3. Email Recipients
While in development our email recipients were hard-coded to be our development team members. As we got close production deployment, we realized there were many more interested parties; therefore, this list was changed to a package variable whose value was a package configuration.

a. EmailRecipients
Stores a semi-colon separated list of email recipients. This package variable is then passed to our stored procedure that sends out an SMTP Email notification.

4. Auditing Related Configurations

The auditing subsystem uses package configurations extensively. Below one package configuration example:

a. ParentPgkID
Each package is assigned a unique identifier by the auditing subsystem. This is a parent package configuration (a package configuration value obtains from the calling or parent package) that allows track parent and child package execution hierarchy from our auditing tables. This has proven very useful since it is very common to have a master package whose job it is to execute all extract and load packages in the correct order – thereby producing an entire batch extract and run.