SSIS Team Blog

API Sample – Data Conversion

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);
}