(By Jamie Thomson)
One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that was inherent in DTS.
Happily, Integration Services (IS) now provides a solution to this problem – Package Configurations. Package configurations are a mechanism for dynamically changing properties of your IS objects and components at run-time using values that are stored externally to the package. There are a number of methods to store and pass these values to a package:
- An XML file
- Environment variable
- Registry settings
- Parent Package Variable
The beauty of an XML file is that each of your packages can point their package configurations at the same XML file which means settings that are pertinent to all packages only have to be changed in one place. In the case of using an XML file to store the values, as long as the XML files are stored in the same place on each environment, (e.g. C:\PackageConfigs\Environment.dtsConfig) it is not necessary to do any editing of the packages when they are moved between environments. This is an example of a direct configuration. An indirect configuration uses environment variables to store the configuration values.
The simplest usage of package configurations would be to store the name of your database server that will be the destination for all your data flows and it is this situation that will be demonstrated herein by use of an XML configuration file. Another method of achieving the same aim would be to store the name of your database server in an environment variable. The environment variable simply needs to be edited on each separate environment.
Setting up your package
First we will need an IS package to which we will add a package configuration. This demonstration will use the simple concept of importing a text file into a SQL Server table.
The text file is a comma-separated-value (CSV) file and should contain the data below, with a file name of PersonAge.txt.
The destination table will be called dbo.PersonAge. It will reside in a database called DataStore. Use the following script to create the database and table:
CREATE DATABASE DataStore
SET ANSI_NULLS ON SET ANSI_PADDING ON
CREATE TABLE [dbo].[PersonAge](
[PersonAgeKey] [int] NULL,
[Person] [varchar](35) NULL,
[PersonAge] [int] NULL
) ON [PRIMARY]
It is assumed that you are familiar enough with IS to build this package so this article will not explain how to do this in detail except to ask that you build your package thus:
- An Execute SQL task, named Truncate destination. The SQL command is TRUNCATE TABLE dbo.PersonAge.
- A Data Flow task, named Import File.
- In your control flow, drag a precedence constraint arrow from Truncate destination to Import File.
Your control flow should look like this:
Now build the data flow inside our Import File task:
- A Flat File connection called Source pointing at your recently created CSV
- An OLE DB Connection called Destination pointing at your recently created database DataStore.
- Import File data flow requires a Flat File Source component pointing at Source. In the advanced editor, Input and Output Properties tab, change the DataType property of external columns Column 0 & Column 2 to DT_I4. Do the same to the output columns Column 0 & Column 2 as well.
- Import File data flow requires an OLE DB Destination pointing at Destination. In the editor select the dbo.PersonAge table as the destination.
- The output from the Flat File Source is the input to the OLE DB Destination.
Your data flow should look like this:
At this stage you should be able to run the package successfully.
Setting up a package configuration
Now for setting up the package configuration. Its very easy to do but brings tremendous flexibility to your packages. With a package configuration you can edit your package properties, variables, connections and the properties of your control flow tasks (termed executables) at run-time. Note that you cannot edit the properties of your data flow components.
- On the menu bar, point to DTS, Package Configurations… or right-click on the control flow design surface and select Package Configurations….
- In the Package Configurations Organizer click Enable package configurations and click Add…
- Click through the welcome screen and in the Configuration Type combo select XML Configuration File.
- In the space for Configuration file name type C:\PackageConfigurations\Environment.dtsConfig and click Next
- In the object tree browse to Connections.Destination.Properties and check the InitialCatalog & ServerName properties. Click Next
- Give your configuration a name and click Finish. See…easy!!!
Your package will now pick up the values for the 2 properties at run-time. If you open the XML file in a text editor you will be able to see that the properties are currently set to whatever they were set to prior to building the package configuration.
Now you could easily move this package (and any other packages in your application) to a completely new environment and all you would have to do is change 1 property in C:\PackageConfigurations\Environment.dtsConfig. Pretty nifty! If you wanted to you could even dynamically populate the name and location of your source file at run-time.
This is a short introduction to package configurations in order to demonstrate the use of them. Package configurations are used to alter the state of your package at run-time therefore enabling the IS developer to build dynamic packages without having to write custom code.
Download the pre-built demonstration material (7KB) Easy Package Configuration.zip.