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
