Programming the Data Conversion task in SQL Server Integration Services 2012 in C#

  • DdG

    Valued Member

    Points: 54

    I am trying to adapt C# code that configures the Data Conversion task from the code used in SQL 2005 and 2008 to 2012. Many things seem to have changed, and I am unable to figure out how to correctly assign the properties for the input->output path in the Conversion task. The following code worked for 2005 and 2008:

    IDTSPath90 path = this.mainPipe.PathCollection.New();

    path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], convComponent.InputCollection[0]);

    IDTSInput90 input = convComponent.InputCollection[0];

    IDTSVirtualInput90 virtualInput = input.GetVirtualInput();

    //Binder start og converted sammen. VirtualInput på converted er output på start

    foreach (ModelColumnTransformation columnTransformation in transformation.Transfers)

    {

    foreach (IDTSVirtualInputColumn90 virtualColumn in virtualInput.VirtualInputColumnCollection)

    {

    if (string.Compare(virtualColumn.Name, columnTransformation.FromColumn.Name, true) == 0 || (columnTransformation.FromColumn.Preformatted && columnTransformation.FromColumn.Name.EndsWith(" as \"" + virtualColumn.Name + "\"")))

    {

    convInstance.SetUsageType(input.ID, virtualInput, virtualColumn.LineageID, DTSUsageType.UT_READONLY);

    IDTSOutputColumn90 outputColumn = convComponent.OutputCollection[0].OutputColumnCollection.New();

    outputColumn.Name = virtualColumn.Name + " (Converted)";

    outputColumn.SetDataTypeProperties(columnTransformation.ToColumn.DataType, columnTransformation.ToColumn.Length, columnTransformation.ToColumn.Precision, columnTransformation.ToColumn.Scale, 0);

    outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;

    outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure;

    IDTSCustomProperty90 outputProp = outputColumn.CustomPropertyCollection.New();

    outputProp.Name = "SourceInputColumnLineageID";//I expect this line to be the problem

    outputProp.Value = virtualColumn.LineageID;//I expect this line to be the problem

    outputProp = outputColumn.CustomPropertyCollection.New();

    outputProp.Name = "FastParse";

    outputProp.Value = false;

    break;

    }

    }

    }

    Apparently, the inner workings of the LineageID property has changed, and if I look at the SSIS format, the LineageID now needs to be a "logical" string instead of an integer ID. I have been unable to figure out where to obtain this ID in the objects and assign it in the transition from input to output.

  • DdG

    Valued Member

    Points: 54

    Problem solved. For 2012, the ContainsID property needs to be set for the internal representations to work. This was not required for 2005 or 2008.

    IDTSCustomProperty90 outputProp = outputColumn.CustomPropertyCollection.New();

    outputProp.ContainsID = true;

    outputProp.Name = "SourceInputColumnLineageID";

    outputProp.Value = virtualColumn.LineageID;

    outputProp = outputColumn.CustomPropertyCollection.New();

    outputProp.Name = "FastParse";

    outputProp.Value = false;

  • bornsql

    SSC-Addicted

    Points: 476

    i also have the same issue , for the changes which you said is not wrking

    Shaheer

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply