API Sample – Data Conversion

First published on MSDN on Jan 02, 2009

This sample creates a data flow package with an OLEDB Source and a Data Convert transform. The transform converts the CustomerKey column (int) to a WSTR(50), and stores the result in the CustomerKeyAsString column.

static void Main(string[] args)
{
Package package = new Package();

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

// Set the name (otherwise it will be a random GUID value)
TaskHost taskHost = dataFlowTask as TaskHost;
taskHost.Name = "Data Flow Task";

// We need a reference to the InnerObject to add items to the data flow
MainPipe pipeline = taskHost.InnerObject as MainPipe;

//
// Add connection manager
//

ConnectionManager connection = package.Connections.Add("OLEDB");
connection.Name = "localhost";
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 transform
//

IDTSComponentMetaData100 dataConvertComponent = pipeline.ComponentMetaDataCollection.New();
dataConvertComponent.ComponentClassID = "DTSTransform.DataConvert";
dataConvertComponent.Name = "Data Convert";
dataConvertComponent.Description = "Data Conversion Component";

CManagedComponentWrapper dataConvertWrapper = dataConvertComponent.Instantiate();
dataConvertWrapper.ProvideComponentProperties();

// Connect the source and the transform
pipeline.PathCollection.New().AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0],
dataConvertComponent.InputCollection[0]);

//
// Configure the transform
//

IDTSVirtualInput100 dataConvertVirtualInput = dataConvertComponent.InputCollection[0].GetVirtualInput();
IDTSOutput100 dataConvertOutput = dataConvertComponent.OutputCollection[0];
IDTSOutputColumnCollection100 dataConvertOutputColumns = dataConvertOutput.OutputColumnCollection;
int sourceColumnLineageId = dataConvertVirtualInput.VirtualInputColumnCollection["CustomerKey"].LineageID;

dataConvertWrapper.SetUsageType(
dataConvertComponent.InputCollection[0].ID,
dataConvertVirtualInput,
sourceColumnLineageId,
DTSUsageType.UT_READONLY);

IDTSOutputColumn100 newOutputColumn = dataConvertWrapper.InsertOutputColumnAt(dataConvertOutput.ID, 0, "CustomerKeyAsString", string.Empty);
newOutputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 50, 0, 0, 0);
newOutputColumn.MappedColumnID = 0;

dataConvertWrapper.SetOutputColumnProperty(
dataConvertOutput.ID,
newOutputColumn.ID,
"SourceInputColumnLineageID",
sourceColumnLineageId);
}