Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Programming the Data Conversion task in SQL Server Integration Services 2012 in C# Expand / Collapse
Author
Message
Posted Thursday, April 19, 2012 1:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:15 AM
Points: 2, Visits: 30
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.
Post #1286250
Posted Tuesday, May 22, 2012 5:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:15 AM
Points: 2, Visits: 30
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;
Post #1304060
Posted Thursday, August 22, 2013 2:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:31 AM
Points: 104, Visits: 313
i also have the same issue , for the changes which you said is not wrking
Shaheer
Post #1487119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse