Using Various Configuration Settings in SQL Server 2005 Integration Services

Introduction

I frequently watch the Microsoft TechNet forums for questions regarding SQL Server 2005 Integration Services. One theme that continually crops up, either in the questions themselves, or the answers given, relates to the use of Configurations in SSIS packages to make the packages easier to maintain as they migrate from Development, to Test, and finally, to Production environments. The methodology I describe here has worked for me in the past and hopefully will work for you as well.

DISCLAIMER: While the T-SQL scripts included here are fairly simple, there is no guarantee that they won’t do something unexpected on YOUR system that they didn’t do on MINE. Please read through and understand what is going on with each script before running it. Shame on you if you blindly copy a script off the internet and run it without first checking what it does!

A Basic Understanding of Environments

With the release of SQL Server 2005, Microsoft made a shift to a more traditional setup regarding the whole DTS/SSIS design environment. Gone are the days when the DBA, or anyone else with proper access, could open a DTS package, edit it (and possibly break it), save it, THEN decide to test it, only to find that: 1) it doesn’t work and 2) the changes cannot be reverted. What IT Manager in their right mind would let a C# or VB.NET coder edit a piece of code in a production environment without that change being fully tested and proven? Such WAS the case with SQL Server 2000 and DTS design environment. With SSIS, you now have the ability to work on your package in a development environment, test it fully, then, once satisfied, send the finished copy off to the test environment, and finally to production.

The Problem

So, you ask, how does one ‘switch’ their package from one environment to the next? Some people out there are going through the arduous task of manually editing each and every connection manager in each and every package when it gets moved from one environment to the next. I recently developed a series of packages that had 50 child packages, each with several Connection Managers. Yes, Shared Data Sources made things a little easier because all the packages used the same set of Data Sources, but there has to be a better way. And with SSIS Configurations, alas, there is.

A Basic Understanding of Configurations

With Configurations, your package has the ability to get settings and values from places EXTERNAL to the package itself. The list of those ‘external places’ includes, amoung others, Registry entries and Environment Variables.  But for this discussion, I will be focusing only on two options: XML Files and SQL Server entries. Open an SSIS Package and right-click on the Control Flow, then select Package Configurations. Check the box Enable package configurations. Familiarize yourself with this dialog box. We’ll be visiting it quite a bit.

Basic Overview

What I’m going to walk you through in this document is a way for a package to first visit an XML Configuration document to retrieve the location of a SQL Server database. This database will hold all the connection string information for all the Connection Managers that the package uses, along with other inportant settings like Variable values, etc. There will be one database per environment (Dev, Test, Prod) running on different instances of SQL Server (which in most cases is different physical servers).

Let’s Get Started

Before you get too far ahead, we need to do some setup work. Open SQL Server Management Studio and connect to your Development server. Create a new database called SSIS_Configuration. (See script below.)

CREATE DATABASE [SSIS_Configuration] ON PRIMARY
NAME = N’SSIS_Configuration’,
FILENAME = ‘D:\MSSQL\SSIS_Configuration.mdf’ ,
SIZE = 10240KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON ( NAME = N’SSIS_Configuration_log’,
FILENAME = N’D:\MSSQL\SSIS_Configuration_log.ldf’ ,
SIZE = 1024KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10 %)
GO

 Note: You may need to edit the script to place the mdf and ldf files in their proper place for your environment.

There, now that wasn’t too bad was it? Let’s keep going and add a table:

USE [SSIS_Configuration]
GO 

CREATE TABLE [dbo].[Package_Config](
–[Application_Name] [varchar](50) NULL,
–[Common_Name] [varchar](50) NULL,
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]

 Notice the first two columns are commented out. The four remaining columns absolutely need to be in this table in order for SSIS to retrieve the info. I just added the other two to mine to give me a better handle of managing the numerous rows that will be saved there. If you want, remove the comments from the first two lines, and/or add an IDENTITY column.

We don’t need to add any data to this table directly. Our first SSIS Package is going to do that for us.

NOTE: This table can be generated from within an SSIS Package, but you will only get the four basic required columns. If you wanted more, you would need to manually edit the table design to add them.

Using Shared Data Sources

In the Business Intelligence Development Studio (BIDS), open the Project/Solution of your SSIS Packages. Under Shared Data Sources, create a new SQL Server Connection to the SSIS_Configuration database above. Make sure you use SQL Native Client as the Provider.

In one of the packages, right-click in the Connection Manager tray and select “New Connection from Data Source”. Choose the SSIS_Configuration connection just added.

NOTE: I design ALL my packages with Shared Data Sources stored at the Package level.

Creating The XML Configuration

Open the Package Configuration Organizer dialog box (remeber that one?) and click “Add”. Select XML as the type, then under the file name, click Browse. Create a new folder on the C:\ drive (this will get saved on the workstation, I named mine C:\SSIS_Config\) and specify a file name. My full path and filename is as follows:

C:\SSIS_Config\SSISConfigDb.dtsConfig

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of the Connection Manager for the SSIS_Configuration database as shown below.

Click Next. Give the Configuration a name that makes sense. Example: “SSIS Config ConnStr”. Configuration names must be unique inside a package, but they are for reference purposes only.

Back in the Package Configuration Organizer, click the Add button again. But this time, select the following options:

  • For Configuration type: “SQL Server”
  • For Connection: “SSIS Configurations” (or whatever you named the Connection Manager for your SSIS_Configuration database)
  • For Configuration Table: “[dbo].[Package_Config]” (choose from the list based on what you named your table)
  • For Configuration Filter, enter something like “Connection String for <MyDatabase>”

NOTE: If you are having trouble accessing the database and/or table you created earlier, you may need to click the “NEW” button as appropriate to generate the CREATE sql statement.

Click Next. Under the “Select Properties to Export” dialog box, navigate to and select ONLY the Connection String property of ONE other Connection Manager in the package. Give the Configuration a name. I typicaly use the same name as the Filter from the above step.

Repeat this process for the other Connection Managers in the Package. NOTE: DO NOT fall to the temptation of having only ONE Configuration ‘Filter’ with MULTIPLE Connection String properties associated with it. MAKE SURE you create a NEW ‘Filter’ for each and every Connection String/Connection Manager. (I’ll explain why later!) When finished, close the dialog box.

Just for the heck of it, open your Windows Explorer and look for the XML file you created on your C:\ drive. Open it in NotePad, Explorer, or some other XML editor. See if the tags and values jibe with what you set up. Now open SSMS and query the one table in your SSIS_Configuration database. Look at the various fields and values. If you added the two extended columns like I did, you can manually edit them to suit your needs, but DO NOT TOUCH any other fields. I typically use “Global Connection String” in the Application_Name field and the name of the database for the Common_Name field.

When the package runs, here is what is going to happen: First, the SSIS Run-Time engine will read the settings in the XML file and determine where the SSIS Configuration database is located (because you saved the Connection String property of the SSIS_Configuration database there). Then, knowing that, it will go to that server, database and table and get the Connection String property of all the other Connection Managers in the package, in the order specified in the Configurations dialog box. Only once that is done, will it start moving through your package, executing the tasks and moving data in the Data Flow.

If you now manually edit one of the ConnectionString properties in your SSIS_Configuration database, the next time a package runs that uses that Configuration entry, it will override the design-time ConnectionString with the ConnectionString from the SSIS_Configuration database.

So far, this is not earth shattering, but stick with it for a little longer.

Reusing Configuration Settings in Other Packages

 On subsequent packages, add the Connection Managers from Shared Data Sources in the same way. Then add Configurations for each, starting with the XML entry for SSIS_Configuration database. BUT, when you add each one, the system will determine that the file (or database, table, and filter) already exists. When it does, you will get a message like displayed below.

 Resue Existing Connection

Choose “Reuse Existing” and this will ‘hook’ the Connection String settings of the Connection Managers to the values stored (in the file or table, as appropriate). DO NOT select “Overwrite!”

A good practice would be to create a template package with, as a minimum, the SSIS_Configuration Connection Manager and its Configuration already in place. Then use the template to create regular packages.

One of the keys to making this work across all packages is to use the same ConnectionManager name  for a connection to the same database. (Shared Data Sources takes care of this for you, which is why I like them so much). If you name one Connection Manager as “Customer Contact DB” in one package, and as “Contact info for Customers” in another, you are going to have trouble. Since, in my work place, the database name is the same in Development, Test, and Production environments, I usually use the name of the Database for the name of the Connection Manager. Keeps things simple and consistent.

Migrating to Test and Production

You should be at a point where all the packages you have are set up with Configurations, and they all look for an XML file on the C:\ drive FIRST, then from there look to a SQL Server database to get the rest of the Connection Strings. So how do you move them to the next environment? And here’s the great thing about this method: You don’t need to edit the packages at all! Follow these basic steps:

  1. Copy the XML Config file from your local C:\ drive to the exact same folder location on the TEST server.
  2. Open the XML file in an editor and manually edit the Server Name portion of the tag to reflect the name of the TEST server (or, more accurately, the name of the SQL Server instance that hosts your test environment).
  3. In the SQL Server instance for the TEST Environment, create the SSIS_Configuration database as before.
  4. Create the table with the exact same meta data.
  5. Copy all rows from the Development table to the table on the TEST server.
  6. Manually edit the Connection String properties (stored in the  “ConfiguredValue” field) to reflect where each database is located in the TEST Environment. If you are lucky, you may only need to edit the “ServerName=xxxzzzyyy” portion of the Connection String.

Now install all your packages on the TEST Server. When they go to run, they will look for the xml file (on the TESTserver’s C:\ drive), figure out that the SSIS_Configuration Database is on the TEST server, and go there to get the location (Connection String) of all the Connection Managers in the package.

Follow the same steps to create the table and database in your Production Environment, making sure the XML file is placed in the same location, and edited as needed.

One Filter per Connection Manager

Why one ‘filter’ per Connection? Suppose you have 4 standard database Connections Managers that you use and create your one SQL Configuration ‘filter’ to save all 4 Connection Strings (resulting in 4 rows of data in the table, but all with the same Filter value). Suppose the NEXT package you create only needs 3 of those 4. When you connect to that filter and choose “Resue Existing” it is going to still hook the three Connection Managers’ Connection String property to the Configuration, BUT when it tries to RUN the package, it will try to configure 4 Connection Managers, but only find 3.  One-for-one, in my opinion, is the way to go here.

Configurations Are Not Just For Connection Strings

You are not limited in saving Connection String information in a Configuration table. I typically also save things like Variable Values as well. I recently created a package that needed to loop over all Excel files in a folder, extract data then move each file after processing. I saved the Folder location in a Configuration because in Dev, I had the files in a folder under My Documents, but in Production, the location was a file server. The Configuration entry for the Path saved me from having to re-design the package to run in Production.

Another typical use is for deciding who should recieve the email if and when a package fails. I create many of my packages with an Event Handler set up at the root package level, and on the OnTaskFailed event. I only include a Send Mail Task, and name it “Failure Email”. I then set up a Configuration entry to save the ToLine property of the “Failure Email” task as a configured value. The ‘package path’ is relative, so it is always the same in every package (does not depend on the package name) and the task name is the same for all packages. So now all I do is edit the Configured Value in each of the three databases, and voila, the package will send an email to a different person (or persons) if it fails in a different environment.

Developer’s Tip: Running against TEST or Prod from your Workstation

There were times when I needed to run my package in the Production environment to verify 100% that it would work, but did not want to (or could not) deploy it to the server and test it there. I was able to set up additional SSIS_Config.dtsConfig files on my C:\ drive, each one pointing to a different server. Since the files all need to be named the same, I created three folders under C:\SSIS_Config\ called DEV\, Test\, and Prod\. Each folder contains an exact copy of the XML file with the one value edited as appropriate. Now, if I need to run my package against Production, I simply COPY (don’t move) from the Prod\ folder and overwrite the one in the location that SSIS will look at. Then when I test run the package from withing BIDS, it runs against Production.

NOTE: If you do this, make sure you 1) keep track of what the ‘current config’ is in your XML file and 2) change it back to DEV when finished.

Passwords in your Config Db

Many times, the connection to a database is NOT through integrated security (as much as Microsoft would want us to use), but through a user name and password. When you go to save a Connection String property of this type to the SSIS_Configuration database, you may notice that the password does NOT get saved with the string. This is by design, and not for the scope of this write-up. Just know that you need to go back an manually edit the ConfiguredValue column of the entry in question and add “Password=abcd1234;” in the string.

Overview

Hopefully, you were able to follow these instructions, even thought the screen shots are a little blurry. It took a while to settle on this methodology, and it came from watching a lot of my initial packages fail, reading a lot of error messages, and deleting and recreating Configuration entries of various types before settling on the system laid out here. But it works. Hopefully, it will work for you as well.

If you have issues with attempting to copy these steps, don’t be afraid to post a comment back to the blog. I’ll get notified and make a personal response if warranted. I also monitor the MS TechNet forum for Integration Services found here: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.integrationsvcs

Good luck, and happy coding!

Advertisements

13 Responses to Using Various Configuration Settings in SQL Server 2005 Integration Services

  1. Joe Horton says:

    I have tried many other types of configurations with DTS packages and this approach by far is the best I have seen and implemented.

    Thanks for the post – job well done!

  2. Kevin Toomey says:

    Todd – excellent posts. I have not seen more than one or two sites devoted to ssis. Please keep posting as you learn more, so that I can learn more.

    Thanks!

  3. Keith Ramsey says:

    This is really helpful. I really appreciate the effort put into this article. I asked a question on this in the SSIS discussion on MSDN and you directed me here.

  4. Phil B says:

    Very helpful !!! Thank you. It took a while to find your post, but it is worth its weight in gold. I sympathize with your comment about going through a lot of error messages before you got to this approach.

  5. Dwaine Wright says:

    Excellent work Todd! It amazes me when MS creates dozens of switches and buttons and then throws it at us without another word. I like options, but a best practice document was BADLY needed and yours is top notch!

    I think you should add a few of the gotcha’s I’ve discovered (or correct me if I’m wrong! :-)), including:
    If you use an UNC path for a config file location it will be accepted at silently be ignored.
    If you’ve defined config file entries at design time, config file entries added at run time on the command line will NOT override OR ADD their values, but will be silently ignored.
    The connection string of a SQL config can’t be changed at runtime using a variable/expression approach.

  6. Dwaine Wright says:

    btw, there’s no way to find this article from your blog’s home page. What other gems are hidden out there?

  7. Dwaine Wright says:

    One comment about using common Data Sources. I’ve found that under certain circumstances the connection manager item gets out of sync with the original data source and, when opening a project, VSS basically forces you to check out the project to make changes.

    During the several attempts to track down a solution, I ran into numerous posts by MVPs suggesting the troubles caused by Data Sources aren’t worth it and to just create connection managers directly.

    Could you comment on your experience with this?

  8. Knightlie says:

    Thanks! This article just saved my arse…

  9. manish srivastava says:

    very useful article. thanks

  10. Andrew says:

    Todd
    This is a great post. I have set it up as a template package, and am using it, I really like this.

    Thanks
    Andrew SQLDBA

  11. AMM says:

    I used this solution to store credentials when using SSIS packages to run extractions against iseries (AS400). It works great. This is by far a more secure method then trying to store credentials in the XML dtsconfig file. Thanks Todd.

  12. jeevan says:

    It’s like hitting the jackpot. I was searching for the exact information from so many days. I have found many links but than they did not cover everything. This is best article with all the things needed in one place for creating dynamic package configuration for the SSIS Development
    .

  13. sue says:

    Todd – this was an early Christmas present and what a bonanza! This is EXACTLY what I was looking for and none of the books I read or the zillion sites I hit during search had instructions outlined in such detail.
    Thank you for sharing the results of all your trials and tribulations! It is much appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: