API Sample – Programmatically create and export configuration files for your packages

Published Mar 25 2019 02:25 PM 82 Views
Not applicable
First published on MSDN on Mar 02, 2009

The previous sample shows how to add a reference to an existing configuration file. This sample shows how to create a new configuration file for a package.

Things to note:

  • The first step is to flag the property you want to export by calling DtsProperty.SetExport(). The first argument is a reference to the object the property belongs to.
  • The CreatePackage() method just creates a package (it doesn’t matter what is in it). Typically you’d be using this code to add a configuration to an existing package (doing a Package.LoadFromXml() or something similar)
class ConfigurationExport
{
static void Main(string[] args)
{
//
// Export a configuration for all connection managers in a package
//
Package package = CreatePackage();

// Enable configurations
package.EnableConfigurations = true;

// Flag all connection manager connection strings as exportable
foreach (var cm in package.Connections)
{
DtsProperty connectionStringProp = cm.Properties["ConnectionString"];
connectionStringProp.SetExport(cm, true);
}

// Export the configuration file
package.ExportConfigurationFile(@"c:\temp\config.dtsconfig");
}

static Package CreatePackage()
{
var p = new Package();

// Add Data Flow Task
Executable dataFlowTask = p.Executables.Add("STOCK:PipelineTask");

// Set the name (otherwise it will be a random GUID value)
var taskHost = dataFlowTask as TaskHost;
Debug.Assert(taskHost != null, "Unexpected task type");

taskHost.Name = "Data Flow Task";

// We need a reference to the InnerObject to add items to the data flow
var pipeline = taskHost.InnerObject as MainPipe;
Debug.Assert(pipeline != null, "Unexpected InnerObject type");

// Create a package variable to store the row count value
p.Variables.Add("RowCountVar", false, "User", 0);

// Add connection manager
ConnectionManager connection = p.Connections.Add("OLEDB");
connection.Name = "MyConnection";
connection.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorksDW2008;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";

// Add OLEDB Source
IDTSComponentMetaData100 srcComponent = pipeline.ComponentMetaDataCollection.New();
srcComponent.ComponentClassID = "DTSAdapter.OleDbSource";
srcComponent.ValidateExternalMetadata = true;
IDTSDesigntimeComponent100 srcDesignTimeComponent = srcComponent.Instantiate();
srcDesignTimeComponent.ProvideComponentProperties();
srcComponent.Name = "OleDb Source";

// Configure it to read from the given table
srcDesignTimeComponent.SetComponentProperty("AccessMode", 0);
srcDesignTimeComponent.SetComponentProperty("OpenRowset", "[DimCustomer]");

// Set the connection manager
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connection);
srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = connection.ID;

// Retrieve the column metadata
srcDesignTimeComponent.AcquireConnections(null);
srcDesignTimeComponent.ReinitializeMetaData();
srcDesignTimeComponent.ReleaseConnections();

// Add Row Count transform
IDTSComponentMetaData100 rowCount = pipeline.ComponentMetaDataCollection.New();
rowCount.ComponentClassID = "DTSTransform.RowCount";
CManagedComponentWrapper instance = rowCount.Instantiate();
instance.ProvideComponentProperties();

// Set the variable name property
instance.SetComponentProperty("VariableName", "User::RowCountVar");

// Connect the OLEDB Source and the Row Count
IDTSPath100 path = pipeline.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], rowCount.InputCollection[0]);

return p;
}
}
Version history
Last update:
‎Mar 25 2019 02:25 PM
Updated by: