Click here to monitor SSC
  • Av rating:
  • Total votes: 73
  • Total comments: 4
Robert Sheldon

XML Configuration files in SQL Server Integration Services

18 August 2011

Package configuration files are a great way of providing the values of SSIS package properties so that packages can be used in a far more versatile way. They make the deployment of SSIS packages easier and can provide parameters that are based on the server configuration, or which change for each runtime. They're easy to understand, especially when explained by Rob Sheldon. 

When you develop a SQL Server Integration Services (SSIS) package, you can add package configurations in order to provide property values to the package at runtime. A package configuration is a defined property/value pair that can be modified without updating the package itself. Package configurations are useful when you want to deploy packages to multiple servers, when you move your packages from a development to production environment, or in any situation in which you want to provide property values to a package at runtime.

SSIS provides several methods for storing package configurations. One of the most flexible of those methods is the XML configuration file. The file lets you store one or more package configurations that can be used by one or more packages. The easiest way to create an XML configuration file is to use the Package Configuration wizard after you’ve set up your package. The wizard walks you through the steps necessary to create the file and lets you choose which property values you want to include in that file.

In this article, I walk you through the steps necessary to create an XML configuration file. To demonstrate these steps, I first used the following Transact-SQL code to create the People table in the AdventureWorks2008R2 database:

USE AdventureWorks2008R2
GO

IF OBJECT_ID('dbo.People') IS NOT NULL
DROP TABLE dbo.People
GO

SELECT TOP 1 *
INTO dbo.People
FROM.Person

Notice that I inserted only one row. I simply wanted to create the table and use the simplest method for doing so. The table will be truncated when you run the package, so it doesn’t matter how many rows you insert into the table.

After I created the People table, I created an SSIS project in SQL Server Business Intelligence Development Studio (BIDS) and renamed the default package LoadPersonData.

You can download the SSIS package from the speech bubble at the top of the article.

I then added two OLE DB connection managers, which each point to the AdventureWorks2008R2 database on the same instance of SQL Server. (Normally, they would point to two different instances, but for testing purposes, this is fine.) The first connection manager is named Server A. The second one is named Server B.

After I added the connection managers, I defined a string variable named ConnectMngr and set its default value to “Server A.” The variable will be used in the control flow to indicate which connection manager to use. I then added an Execute SQL task and two Data Flow tasks to the control flow, as shown in Figure 1.

The control flow in the LoadPersonData SSIS package

Figure 1: The control flow in the LoadPersonData SSIS package

The Execute SQL task truncates the People table. The precedence constraints that connect to the Data Flow tasks are each configured to evaluate to an expression. For example, Figure 2 shows how I configured the precedence constraint that connects to the Server A Data Flow task. Notice that the expression specifies that the ConnectMngr variable must equal “Server A” in order to evaluate to True.

Configuring the precedence constraint

Figure 2: Configuring the precedence constraint to evaluate to an expression

I configured the second precedence constraint just like the first one, except that the expression specifies “Server B” as the variable value.

Next I configured each Data Flow task with an OLE DB source and an OLE DB destination. The sources and destination use their respective connection managers. For example, the Server A data flow uses the Server A connection manager. Figure 3 shows the Server A data flow components. Each data flow retrieves data from the Person table in the AdventureWorks2008R2 database and inserts that data in the People table.

Configuring the data flow for the Server A connection manager

Figure 3: Configuring the data flow for the Server A connection manager

That’s all there is to setting up your SSIS package. Although this is a very simple package, it’s all we need to demonstrate how to implement XML configuration files. (Actually, we don’t even need that much.) If you don’t want to create this package, and instead want to use a package you’ve already created, you should have no trouble applying the steps in the rest of the article to your situation.

Setting Up Your XML Configuration File

After you’ve set up your package, the first step in setting up the XML configuration file is to enable package configurations. To do so, click the Package Configurations option on the SSIS menu. This launches the Package Configuration Organizer, shown in Figure 4.

The Package Configuration Organizer in SSIS

Figure 4: The Package Configuration Organizer in SSIS

To enable package configurations on your package, select the Enable package configurations checkbox. You can then add your package configurations to the package. To do so, click Add to launch the Package Configuration wizard. When the wizard appears, click Next to skip the Welcome screen. The Select Configuration Type screen will appear, as shown in Figure 5.

The Select Configuration Type screen in the Package Configuration wizard

Figure 5: The Select Configuration Type screen in the Package Configuration wizard

From the Configuration type drop-down list, select XML configuration file. You can then choose to specify your configuration settings directly or specify a Windows environment variable that stores the path and file names for the configuration file. For this example, I selected the Specify configuration settings directly option and specified the following path and file name: C:\Projects\SsisConfigFiles\LoadPersonData.dtsConfig. The main thing to notice is that the file should use the extension dtsConfig.

NOTE: If you specify an XML file that already exists, you’ll be prompted whether to use that file or whether to overwrite the file’s existing settings and use the package’s current settings. If you use the file’s settings, you’ll skip the next screen, otherwise, the wizard will proceed as if the file had not existed. Also, if you choose to use an environment variable to store the path and file names, the wizard will not create a configuration file and will again skip the next screen. Even if you use an environment variable, you might want to create the file first and then select the environment variable option afterwards.

The next screen in the wizard is Select Properties to Export. As the name implies, this is where you select the properties for which you want package configurations. In this case, I selected the Value property for the ConnectMngr variable and the ServerName property for each of the two connections managers, as shown in Figure 6.

Selecting properties in the Package Configuration wizard

Figure 6: Selecting properties in the Package Configuration wizard

Because I chose three properties, three package configurations will be created in the XML file. You can choose as many properties as you want to add to your file.

On the next screen of the Package Configuration wizard, you provide a name for the configuration and review the settings (shown in Figure 7).

Completing the Wizard screen in the Package Configuration wizard

Figure 7: The Completing the Wizard screen in the Package Configuration wizard

If you’re satisfied with the settings, click Finish. The wizard will automatically generate the XML configuration file and add the properties that you’ve specified. The file will also be listed in the Package Configuration Organizer, as shown in Figure 8.

The XML package configuration as it’s listed in the Package Configuration Organizer

Figure 8: The XML package configuration as it’s listed in the Package Configuration Organizer

NOTE: When you add an XML configuration file, no values are displayed in the Target Object and Target Property columns of the Package Configuration Organizer. This is because XML configuration files support multiple package configurations.

You should also verify whether the XML package configuration file has been created in the specified location. For this example, I added the file to the C:\Projects\SsisConfigFiles\ folder. The file is automatically saved with the dtsConfig extension. If you open the file in a text editor or browser, you should see the XML necessary for a configuration file. Figure 9 shows the LoadPersonData.dtsConfig file as it appears in Internet Explorer.

The XML in the LoadPersonData.dtsConfig file

Figure 9: The XML in the LoadPersonData.dtsConfig file

As Figure 9 shows, the XML configuration file includes the <DTSConfigurationHeading> element. The element contains the attributes and their values that define when, who, and how the file was generated. The file also includes one <Configuration> element for each package configuration. Each <Configuration> element includes the attributes and their values necessary to determine which property is being referenced. Within each <Configuration> element is a nested <ConfiguredValue> element, which provides the property’s actual value.

Notice that the property values are the same as that of the package itself. When you first set up an XML configuration file, the current package value is used for each property. You can, of course, change those values, as I demonstrate later in the article.

Running Your SSIS Package

After you’ve created your XML configuration file, you’re ready to run your package. You run the package as you would any other SSIS package. However, because package configurations have been enabled, the package will check for any settings that have been predefined.

For the example I’ve been demonstrating here, the package will run as if nothing has changed because, as stated above, the XML configuration file contains the same values as the properties initially defined on the package. That means the ConnectMngr variable will still have a value of “Server A,” and the connection managers will still point to the same SQL Server computer. Figure 10 shows the package after it ran without modifying the XML configuration file.

Running the LoadPersonData package with the default settings

Figure 10: Running the LoadPersonData package with the default settings

As you would expect, the Server A data flow ran, but not the Server B data flow. However, the advantage to using XML configuration files is that you can modify property settings without modifying the package itself. When the package runs, it checks the configuration file. If the file exists, it uses the values form the listed properties. That means if I change the property values in the file, the package will use those new values when it runs.

For instance, if I change the value of the ConnectMngr variable from “Server A” to “Server B,” the package will use the value. As a result, the precedence constraint that connects to the Server A Data Flow task will evaluate to False, and the precedence constraint that connects to the Server B Data Flow task will evaluate to True, and the Server B data flow will run. Figure 11 shows what happens if I change the variable’s value in the XML configuration file to “Server B.”

Running the Server B Data Flow task in the LoadPersonData SSIS package

Figure 11: Running the Server B Data Flow task in the LoadPersonData SSIS package

As you would expect, the Server B Data Flow task ran, but not the Server A Data Flow task. If I had changed the values of the ServerName properties for the connection managers, my source and destination servers would also have been different.

Clearly, XML configuration files offer a great deal of flexibility for supplying property values to your packages. They are particularly handy when deploying your packages to different environments. Server and instance names can be easily changed, as can any other value. If you hard-code the path and file name of the XML configuration file into the package, as I’ve done in this example, then you must modify the package if that file location or name changes. You can get around this by using a Windows environment variable, but that’s not always a practical solution. In addition, you can override the configuration path and file names by using the /CONFIGURATION option with the DTExec utility.

Whatever approach you take, you’ll find XML configuration files to be a useful tool that can help streamline your development and deployment efforts. They’re easy to set up and maintain, and well worth the time it takes to learn how to use them and how to implement them into your solutions.

Robert Sheldon

Author profile:

After being dropped 35 feet from a helicopter and spending the next year recovering, Robert Sheldon left the Colorado Rockies and emergency rescue work to pursue safer and less painful interests—thus his entry into the world of technology. He is now a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. He has also written news stories, feature articles, restaurant reviews, legal summaries, and the novel 'Dancing the River Lightly'. You can find more information at http://www.rhsheldon.com.

Search for other articles by Robert Sheldon

Rate this article:   Avg rating: from a total of 73 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Relative paths to config files
Posted by: nmcdermaid (view profile)
Posted on: Monday, August 22, 2011 at 2:14 AM
Message: What would be really helpful is if you could use relative paths to configuration files. Relative to the DTSX file location that is (not the DTEXEC.EXE as is the current case). Then migration would simply be a case of copying the DTSX file.

We have a situation where we have a shared dev/test server, and the solution we ended up with required user based views against a configuration table.

It would have been so much easier if I could specify a relative path to a config file.

I did quite a bit of research on the topic and found a lot of same-ish articles trumpeting the flexibility of configurations (which they are to a certain extent), but no obvious solution to my specific problem.

... just venting!

Subject: 1st task connection, can't download package
Posted by: tbreach (view profile)
Posted on: Thursday, August 25, 2011 at 9:44 AM
Message: How does the first SQL task connect? I don't see an option to download the package at the speech bubble.

Subject: Download package
Posted by: Dave Convery (view profile)
Posted on: Friday, August 26, 2011 at 3:44 AM
Message: Apologies tbreach, you should be able to download the package now.

Subject: thanks
Posted by: rohit richhariya (view profile)
Posted on: Sunday, March 04, 2012 at 9:49 AM
Message: thanks Robert .........it helps a lot.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.