31 Days of SSIS
The last two posts in the 31 Days of SSIS talked about the use of environmental variables and SQL Server configurations. Between those two types of configurations, you can cover most situations where you need to configure an SSIS package.
There is one more type of SSIS package configuration that I’d like to make sure that I cover in this series. That is the XML configuration file. XML configuration files are, usually, setup at design time; which is then be applied at run-time. I say usually because if you need to ninja in a configuration change after a package is deployed, you can create the XML configuration independent of the SSIS package and apply it to the package without having to associate it to the package.
In essence, XML configuration files act pretty much like config files for .NET applications. They list out a number of properties and apply them to the SSIS package. The XML configuration files are broken into two parts, the heading and the configuration details
Heading
The heading lets you know who created the XML document and when, and for which package. This information isn’t used when the package executes, and I don’t put much effort into making certain this always relates to the package. This is mostly because it doesn’t matter, but also because I share XML configuration files between packages (I’ll talk more about this later).
The heading, along with the first couple tags of the configuration looks like this:
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo
GeneratedBy="Just tell everyone StrateSQL told you to do this."
GeneratedFromPackageName="value doesn’t matter"
GeneratedFromPackageID="{value still doesn’t matter}"
GeneratedDate="1/19/2011 12:00:00 PM"/>
</DTSConfigurationHeading>
There is information on who created, which package, the package ID, and the date generated. As I mentioned, this information is just informational and has no bearing on execution.
Configuration
The configuration portion of the XML configuration file is where all of the pertinent details for configuring your SSIS package will be kept. The configuration contains the path to the property and the value for the property. Each configuration will look like the XML fragment below:
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::ClientCode].Value" ValueType="String"> <ConfiguredValue>Z0M8I3</ConfiguredValue> </Configuration> </DTSConfiguration>
Your XML configuration files will have a string of configuration nodes put together with the element at the bottom to close the document.
Why XML Configuration?
Why would we want to use XML configurations when we can easily use environmental variables and SQL Server configurations? Those two seem to cover the gauntlet of what we need to configure. Do we need a third option?
We do. This is needed because there was a common theme I included in the other two configuration types that limit their usefulness. Environmental variables and SQL Server configurations are applied at the environmental level; which means that every execution of the SSIS package will use those values. If a package needs to run a certain way always, these two will fit the bill.
If you are like me, though, you try to write SSIS packages that encapsulate business processes and are generic enough that they can be reused in different scenarios that utilize that process but not necessarily the same conditions.
The perfect example of this is when your business has multiple clients send you data. Do you write one process for all of your clients or one process that all your clients can use? I prefer the later and develop as many of my packages as possible with that idea in mind.
XML configurations provide just the vehicle for filling this need. You can have one version of the configuration file for client Z0M8I3 and another version for the B4C0N client. When you execute the package, just pick the correct XML configuration file and the package will run under that setup.
Along with using multiple XML configuration files with an SSIS package, you can also use a XML configuration file with multiple SSIS package. This helps reduce the number of configuration files needed and reduces the overhead to choosing this type of configuration.
XML Configuration Setup
Setting up XML configurations is similar to how environmental variables and SQL Server configurations are setup. To start, open the Package Configuration Organizer window from the main menu or right-click menu in the Control Flow. Since we already have configurations setup we don’t need to enable Enable Package configurations. If it isn’t, do that now. Then select the Add… button.
On the next screen select XML Configuration File from the Configuration type. Then choose a file name in the Configuration file name. Don’t be overly concerned with this name since in the grand scheme of things it may not matter. More on that later. After you have a file name, client the Next… button.
The next screen is Select Properties to Export where the properties that will be configured will be selected. For this example, select the Value property of the ClientCode variable. This will place the property into the XML configuration file. If there are others to add, they can be added to the configuration file as well. When you are finished, click the Next… button.
Got Bacon!
On the last screen, give the XML configuration a meaningful name. Then click the Finsh button.
Setup Complete
Now that the setup is complete, we should execute this a couple times. First, just try running it as is. The value for ClientCode from the XML configuration file will be the same as the SSIS package. When it runs you’ll get a message box with the B4C0N in the upper corner.
Got Zombies!
Browse out to the XML configuration file that was previously created and change the value for ClientCode in that file from B4C0N to Z0M8I3. Save the file and then execute the SSIS package one more time. Amazingly, and as expected, the value that will be returned in the upper corner of the message box will be Z0M8I3.
XML Configuration Wrap-Up
That about does it for setting up XML configuration files. Before finishing this post, there are a couple things that need mentioning regarding the use of XML configuration files.
To start, avoid the temptation to only use these to configure SSIS packages. Keep environmental configuration data in environmental variables and SQL Server configurations. XML configuration files should contain information that is specific to that particular execution of the SSIS package. Not the values that will be set for every execution.
Also, when you deploy your SSIS packages you will likely not have the same folder structure in that environment as exists on your development machine. This is to be expected. This can cause an validation message to appear when you run you SSIS packages that looks like the following:
Information: The package is attempting to configure from the environment variable “SSISConfig”.
Information: The package is attempting to configure from SQL Server using the configuration string “”SSISConfig”;”[dbo].[SSISConfigurations]“;”OverrideDates”;”.
Information: The package is attempting to configure from the XML file “D:\Users\jstrate\Development\SSIS Examples\SSIS Examples\ClientCode.dtsConfig”.
This means that the SSIS package couldn’t find the XML configuration file where you configured it to be. This isn’t terrible. Since you will associate the XML configuration with the SSIS package through the DTEXEC command line or SQL Agent job the error isn’t a big deal. You can configure your packages to look for the files in a common location that will be in all environments or do what I do; which is to delete the configuration entry after the XML configuration file has been created. Remember, I mentioned that I share XML configuration files between SSIS packages. I don’t create a new one to share for each package each time I think about sharing them.
Package Download
Related posts:
- 31 Days of SSIS – SQL Server Configuration (18/31)
- 31 Days of SSIS – Environmental Variable(17/31)
- 31 Days of SSIS – Folder Structures (4/31)
