Continuing my theme of building various types of packages in code, this example shows how to building a package with package configurations. Incidentally it shows you how to add a variable, and a connection too. It covers
the five most common configurations: Configuration File Indirect Configuration File SQL Server Indirect SQL Server Environment Variable For a general overview try
the SQL Server Books Online Package Configurations topic.
The sample uses a a simple helper function ApplyConfig to create or update a configuration, although in
the example we will only ever create.
The most useful knowledge is
the configuration string (Configuration.ConfigurationString) that you need to set. Configuration Type Configuration String Description Configuration File
The full path and file
name of an XML configuration file.
The file can contain one or more configuration and includes
the target path and new value to set. Indirect Configuration File An environment variable
the value of which contains full path and file
name of an XML configuration file as per
the Configuration File type described above. SQL Server A three part configuration string, with each part being quote delimited and separated by a semi-colon. --
The first part is
the connection manager
name.
The connection tells you which server and database to look for
the configuration table. --
The second part is
the name of
the configuration table.
The table is of a standard format, use
the Package Configuration Wizard to help create an example, or see
the sample script files below.
The table contains one or more rows or configuration items each with a target path and new value. --
The third and final part is
the optional filter
name. A configuration table can contain multiple configurations, and
the filter is literal value that can be used to group items together and act as a filter clause when configurations are being read. If you do not need a filter, just leave
the value empty. Indirect SQL Server An environment variable
the value of which is
the three part configuration string as per
the SQL Server type described above. Environment Variable An environment variable
the value of which is
the value to set in
the package. This is slightly different to
the other examples as
the configuration definition in
the package also includes
the target information. In our ApplyConfig function this is
the only example that actually supplies a target value for
the Configuration.PackagePath property.
The path is an XPath style path for
the target property, \Package.Variables[User::Variable].Properties[Value],
the equivalent of which can be seen in
the screenshot below, with
the object being our variable called Variable, and
the property to set is
the Value property of that variable object.
The configurations as seen when opening
the generated package in BIDS:
The sample code creates
the package, adds a variable and connection manager, enables configurations, and then adds our example configurations.
The package is then saved to disk, useful for checking
the package and testing, before finally executing, just to prove it is valid. There are some external resources used here, namely some environment variables and a table, see below for more details. namespace Konesans.Dts.Samples
{
using System;
using Microsoft.SqlServer.Dts.Runtime;
public class PackageConfigurations
{
public void CreatePackage()
{
// Create a new package
Package package = new Package();
package.
Name = "ConfigurationSample";
// Add a variable,
the target for our configurations
package.Variables.Add("Variable", false, "User", 0);
// Add a connection, for SQL configurations
// Add
the SQL OLE-DB connection
ConnectionManager connectionManagerOleDb = package.Connections.Add("OLEDB");
connectionManagerOleDb.
Name = "SQLConnection";
connectionManagerOleDb.ConnectionString =
"Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;";
// Add our example configurations, first must enable package setting
package.EnableConfigurations = true;
// Direct configuration file, see sample file
this.ApplyConfig(package, "Configuration File", DTSConfigurationType.ConfigFile,
"C:\\Temp\\XmlConfig.dtsConfig", string.Empty);
// Indirect configuration file,
the emvironment variable XmlConfigFileEnvironmentVariable
// contains
the path to
the configuration file, e.g. C:\Temp\XmlConfig.dtsConfig
this.ApplyConfig(package, "Indirect Configuration File", DTSConfigurationType.IConfigFile,
"XmlConfigFileEnvironmentVariable", string.Empty);
// Direct SQL Server configuration, uses
the SQLConnection package connection to read
// configurations from
the [dbo].[SSIS Configurations] table, with a filter of "SampleFilter"
this.ApplyConfig(package, "SQL Server", DTSConfigurationType.SqlServer,
"\"SQLConnection\";\"[dbo].[SSIS Configurations]\";\"SampleFilter\";", string.Empty);
// Indirect SQL Server configuration,
the environment variable "SQLServerEnvironmentVariable"
// contains
the configuration string e.g. "SQLConnection";"[dbo].[SSIS Configurations]";"SampleFilter";
this.ApplyConfig(package, "Indirect SQL Server", DTSConfigurationType.ISqlServer,
"SQLServerEnvironmentVariable", string.Empty);
// Direct environment variable,
the value of
the EnvironmentVariable environment variable is
// applied to
the target property,
the value of
the "User::Variable" package variable
this.ApplyConfig(package, "EnvironmentVariable", DTSConfigurationType.EnvVariable,
"EnvironmentVariable", "\\Package.Variables[User::Variable].Properties[Value]");
#if DEBUG
// Save package to disk, DEBUG only
new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
Console.WriteLine(@"C:\Temp\{0}.dtsx", package.Name);
#endif
// Execute package
package.Execute();
// Basic check for warnings
foreach (DtsWarning warning in package.Warnings)
{
Console.WriteLine("WarningCode : {0}", warning.WarningCode);
Console.WriteLine(" SubComponent : {0}", warning.SubComponent);
Console.WriteLine(" Description : {0}", warning.Description);
Console.WriteLine();
}
// Basic check for errors
foreach (DtsError error in package.Errors)
{
Console.WriteLine("ErrorCode : {0}", error.ErrorCode);
Console.WriteLine(" SubComponent : {0}", error.SubComponent);
Console.WriteLine(" Description : {0}", error.Description);
Console.WriteLine();
}
package.Dispose();
}
/// <summary>
/// Add or update an package configuration.
/// </summary>
/// <param name="package">The package.</param>
/// <param name="name">The configuration
name.</param>
/// <param name="type">The type of configuration</param>
/// <param name="setting">The configuration setting.</param>
/// <param name="target">The target of
the configuration, leave blank if not required.</param>
internal void ApplyConfig(Package package, string
name, DTSConfigurationType type, string setting, string target)
{
Configurations configurations = package.Configurations;
Configuration configuration;
if (configurations.Contains(name))
{
configuration = configurations[name];
}
else
{
configuration = configurations.Add();
}
configuration.
Name = name;
configuration.ConfigurationType = type;
configuration.ConfigurationString = setting;
configuration.PackagePath = target;
}
}
}
The following table lists
the environment variables required for
the full example to work along with some sample values.
Variable
Sample value
EnvironmentVariable
1
SQLServerEnvironmentVariable
"SQLConnection";"[dbo].[SSIS Configurations]";"SampleFilter";
XmlConfigFileEnvironmentVariable
C:\Temp\XmlConfig.dtsConfig
Sample code, package and configuration file.
ConfigurationApplication.cs ConfigurationSample.dtsx XmlConfig.dtsConfig
