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

• DdG

  SSC Enthusiast

  Points: 194

  More actions

  April 19, 2012 at 1:41 am

  #74312

  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

  SSC Enthusiast

  Points: 194

  More actions

  May 22, 2012 at 5:32 am

  #1490990

  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: 496

  More actions

  August 22, 2013 at 2:50 am

  #1643798

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

  Shaheer