Custom transform component, change type or add output column

Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.

Many thanks

You cannot change the type of a buffer column. You will have to add a new column. If you only expect users to select valid columns, and for each one selected you could handle this by overriding SetUSageType on the component class.

Whenever people select a column (UsageType read-only, deny read-write as not required), you could then add a new output column. This would work and would be quite clean.

I would also use a custom property on the output column to store the lineage ID of the "source" inpurt column.

The better way would be to handle adding the column outside of SetUsageType. So with the Advanced UI you would need to select the input column (SetUsageType), then add the output column (InsertOutputColumnAt) and add the custom property (SetOutputColumnProperty) all in three steps. Method names used by the UI are shown in braclets so you know what to do if it was your own UI. With a proper custom UI this of course would become one step for the end user, but more importantly could all be done through the managed wrapper interface, CManagedComponentWrapperClass.SetUsageType, InsertOutputColumnAt, SetOutputColumnProperty methods. This would be good for the user experience it will give around real-time validation and OK/Cancel behaviour of the UI.

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT

Custom Property Question

I have a custom transform, with a custom property of value 1. My question is, can I change that value during runtime with the value from a variable?

Thanks

Yes, you would use a property expression.

When you define the property in the transform itself, you need to enable it for expressions through. Set the IDTSCustomProperty90 ExpressionType property to DTSCustomPropertyExpressionType.CPET_NOTIFY.

|||

I did that in ProvideComponentProperties,

IDTSCustomProperty90 exp = outputColumn.CustomPropertyCollection.New();

exp.Name = "Expression";

exp.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

When I execute the package, I can go into expressions and put in a value but it does not change when I run the package. Is there something I am missing?

Thanks

|||

That should work. Minor point but I thinkl calling the property Expresion could be a bit confusing, but at the end of the day that properties value should be the evaluated expresison result when you read-it at runtime.

Could you send me the code ? darren.green at sqldts, com

|||

Please check your email.

|||Mail checked, reply sent. Assuming I answer your question, can you post a summary here for the benefit of others who may be following or subsequently find this thread and have a similar problem.|||

Hi,

I have exactly the same problem, and I would like to share your solution if possible

Thanks a lot in advance

LVT

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

Custom Dataflow Transform in Programmatically Built Package

I'm building packages programmatically and all is well. I have a new custom transform that I developed. It also works fine. Now I'm trying to add my new component to my packages when I programmatically build them, and I'm unable to do that.

Has anyone added their own custom components to a programmatically built package successfully?

I get a COM error on the line that calls ProvideComponentProperties. I've attempted various modifications including not overriding ProvideComponentProperties or just having it do nothing. I always get the same result. What I don't understand is that the custom transform works and handles ProvideComponentProperties fine when it is added to a package in BIDS.

Thanks.

'Add new dataflow task

combitronics = dataFlow.ComponentMetaDataCollection.New()

'Set assembly to my component

combitronics.ComponentClassID = "Ewrap.SSIS.Combitronics"

'Get instance of component

Dim instance As CManagedComponentWrapper = combitronics.Instantiate()

'Initialize component ***Error Occurs Here***

instance.ProvideComponentProperties()

...

Have you tried debugging your custom component. The only other thing I could suggest is where the component is deployed to.|||

Thanks for the response.

It looks like I'm using the wrong name for my assembly when calling it. I found I could specify the GUID instead of the Namespace.Name format and that solved the problem. I got the correct GUID by looking at the .dtsx file after renaming it to .xml so it's easily viewed in IE.