Showing posts with label duplicates. Show all posts
Showing posts with label duplicates. Show all posts

Sunday, March 11, 2012

Custom Property for Remove Duplicates transform Input Row

Im working through the MS example of "removeDuplicates". I cant seem to figure out how to add custom property for input column.

I added the helper method:
private static void AddIsKeyCustomPropertyToInput(IDTSInput90 input, object value)
{
IDTSCustomProperty90 isKey = input.CustomPropertyCollection.New();
isKey.Name = "IsKey";
isKey.Value = value;
}
I call it from:
public override void ProvideComponentProperties()
{
//...
AddIsKeyCustomPropertyToInput(input, false);
//...
}
public override void ReinitializeMetaData()
{
IDTSInput90 input = ComponentMetaData.InputCollection[0];
if (input.CustomPropertyCollection.Count == 0)
{
AddIsKeyCustomPropertyToInput(input, false);
}
// ...
}

However when I deployed it and added the component to SSIS package - I cant see the Custom Column "IsKey" in the input column properties window.
What am I missing - please help As stated by some fore-sightful guy in a blog -

the way to SSIS enlightment is treacherous

. it seems i was missing something very basic - i was trying to set custom property for the whole input instead of just input column.

So instead of 2 above mentioned calls (in ProvideComponentProperties and ReinitializeMetaData ) i needed to set it in following:

public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType)
{
if (virtualInput == null)
{
throw new ArgumentNullException("virtualInput");
}

IDTSVirtualInputColumn90 vCol = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
IDTSInputColumn90 col = null;

/// No support for BLOB image columns.
if (vCol.DataType == DataType.DT_IMAGE)
throw new Exception("Binary data types not supported.");

/// If the usageType is UT_IGNORED, then the column is being removed.
/// So remove it from the outputs also.
if (usageType == DTSUsageType.UT_IGNORED)
{
/// ...
}
else
{
/// Let the base class add the input column.
col = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
// Store the lineageID of the input column in a custom property of the output column.
IDTSCustomProperty90 inputColIsKey = col.CustomPropertyCollection.New();
inputColIsKey.Name = "IsKey";
inputColIsKey.Value = false;

/// Add an output column to the distinct and duplicate outputs.
AddOutputColumn(ComponentMetaData.OutputCollection[0].ID, col);
AddOutputColumn(ComponentMetaData.OutputCollection[1].ID, col);
}
return col;
}

Hopefully - when i have created this transform to filter duplicates based on (Primery) Key column(s) i can post it in my blog (to be created)|||Now if only I could figure out how to pass this custom "IsKey" property to class "Row" Equals operator and to class "Buffer" sort method