SSIS – Dynamically Adding Package Configurations

For my ETL design, most activity (or real work) is conducted through child packages that are controlled through master packages. This gives us a good amount of flexibility to pass variables (like audit keys and configuration file names) to the child from the master.

However, creating and maintaining the configurations in the child can be laborious. The UI is ok for a small number of changes but for large numbers, a programmatic approach is better. This post looks at managing configurations through code.

Assemblies and Constructs

The configuration class belongs to the package. This is automatically included in SSIS scripting tasks. If the code is simply a C# program the assembly the Dts.Runtime assembly is Microsoft.SQLServer.ManagedDTS.dll

And the package can be referenced in the following manner

Application _a = new

Package _p = _a.LoadPackage(@”Your Package File Name”, null);

Configuration Class

The package must be enabled for configurations. Simply set the EnableConfigurations property to true

_p.EnableConfigurations = true;

Adding a Configuration

A new configuration can be added with the .Add method. The new configuration is added at the end (becomes the last) and therefore we can refer to it by its ordinal. All new properties can be maintained in this manner.

To add a new configuration (config_file) to the package which assigns the package variable (config_file) the value of the parent packages config_file variable, we would use the following code;

_p.Configurations[_p.Configurations.Count – 1].Name = “config_file”;
_p.Configurations[_p.Configurations.Count – 1].ConfigurationType = DTSConfigurationType.ParentVariable;
_p.Configurations[_p.Configurations.Count – 1].ConfigurationString = “config_file”;
_p.Configurations[_p.Configurations.Count – 1].PackagePath = “\\Package.Variables[User::config_file].Properties[Value]”;
_p.Configurations[_p.Configurations.Count – 1].Description = “Inherit config_file from master”;

Iteration and Deletion

The configuration object is innumerable. Therefore we can iterate over existing package configurations using an iterator (as below). Similarly, we could iterate by ordinal (using its index position). To remove the configuration, simply use the remove method.

foreach (Configuration _c in _p.Configurations)

Save the Package

The package must be saved before any changes made through code are reflected in bids.

_a.SaveToXml(@”Your package file name”, _p, null);

Bringing it Together

This code can be used in a package to append a variable to a package and add its configuration. I simply used a foreach container with a script task and the following code (note the variable file_name is the fully qualified file name passed from the container);

string _file_name = Dts.Variables[“file_name”].Value.ToString();
string _file = System.IO.Path.GetFileName(_file_name);
_file = _file.Substring(0, _file.Length – 5);

Microsoft.SqlServer.Dts.Runtime.Application _a = new Microsoft.SqlServer.Dts.Runtime.Application();

Package _p = _a.LoadPackage(_file_name, null);
_p.Variables.Add(“config_file”, false, _file, “”);
_p.EnableConfigurations = true;
_p.Configurations[_p.Configurations.Count – 1].Name = “config_file”;
_p.Configurations[_p.Configurations.Count – 1].ConfigurationType = DTSConfigurationType.ParentVariable;
_p.Configurations[_p.Configurations.Count – 1].ConfigurationString = “config_file”;
_p.Configurations[_p.Configurations.Count – 1].PackagePath = \\Package.Variables[User::config_file].Properties[Value];
_p.Configurations[_p.Configurations.Count – 1].Description = “Inherit config_file from master”;
_a.SaveToXml(_file_name, _p, null);
Dts.TaskResult = (int)ScriptResults.Success;

Leave a Reply

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

You are commenting using your 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