SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
DdG
DdG
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 39
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
DdG
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 39
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
bornsql
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 341
i also have the same issue , for the changes which you said is not wrking
Shaheer
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search