Programmatically create a Data Conversion Transformation

Hello,

does anyone know of a good example of how to programmatically add a Data Conversion Transformation to a package? I have come so far that I have the component in my dataflow task, but I don't know how to set the properties of it. That is, how to determine the columns that should be converted and to what data type etc.

I (as far as I understand) need a data conversion transformation since I have not managed to create (via the designer) a package that reads data from an AS400 via DB2OLEDB and stores it in a SQL Server 2005 Database. I keep getting the error "string cannot be converted from unicode to non-unicode" (or something like that) and by searching this forum I learned that the data conversion component might do the trick.

I added this conversion to a manually designed package and it solved the error, but I don't know how to re-create this package programmatically. I would really appreciate some help on this.

Also, if someone has managed to import data from AS400 to SQL server 2005 via OLEDB and NOT got the string conversion error, please let me know!

Regards,

Annika

This sample should give you an idea:

dataConvert = pipeline.ComponentMetaDataCollection.New();

dataConvert.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";

IDTSDesigntimeComponent90 compDataConvert = dataConvert.Instantiate();

compDataConvert.ProvideComponentProperties();

// Add path between the upstream component and the data convert.

int pathID;

AddPath(source, dataConvert, out pathID);

IDTSInput90 input = dataConvert.InputCollection[0];

IDTSOutput90 output = dataConvert.OutputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Find the column to be converted in the upstream collection.

// Assuming to use the first one.

int columnToConvertLineageID = vInput.VirtualInputColumnCollection[0].LineageID;

IDTSInputColumn90 inputColumn = compDataConvert.SetUsageType(input.ID, vInput, columnToConvertLineageID, DTSUsageType.UT_READONLY);

// Insert the output column. Assuming to insert at the index 0.

IDTSOutputColumn90 dcOutputColumn = compDataConvert.InsertOutputColumnAt(output.ID, 0, "Out col. name", "Col. description");

if (inputColumn != null && dcOutputColumn != null)

{

// Set data type properties of the output column -- to convert to

compDataConvert.SetOutputColumnDataTypeProperties(output.ID, dcOutputColumn.ID, ...);

// Map input and output column.

compDataConvert.SetOutputColumnProperty(output.ID, dcOutputColumn.ID, "SourceInputColumnLineageID", outputColumn.LineageID);

}

|||

Hi,

I just wanted to let you know that I tried your suggestion and it works fine! Thanks!

//Annika