Friday, February 24, 2012

Adding expression in Data Flow Task

Hi

I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.

Thanks in advance,

Vipul

Try using the Derived Column tool.|||

Hi TGnat

I am looking for some apis which will help me to write a code. I am not sure abt Derive Column Tool as there are some of the things which are not supported in it and i am looking to enhance the functionality for my use.

Thanks

Vipul

|||

Can you give an example of what you are trying to do.

When I first started out I thought I would need a script task or a custom control for everything... Now I find that the stock components can meet most of my needs!

|||

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

|||

Vipul123 wrote:

I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.

Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.

Thanks

Vipul

"Stock components" simply means the stuff that is already in the toolbox.

If you want to build something that does the same as a LOOKUP and a DERIVED COLUMN, why not just use the LOOKUP and the DERIVED COLUMN?

-Jamie

|||

Also... Your lookup can return multiple columns, one or more for the look up match, and then you can select other columns to be added to your data flow. Follow this up with a derived column that implements your logic and you may have the functionality you need.

|||

Jamie:

Thanks for the reply. I have few more queries. In the OLEDB Command, there is a tab called "Column Mapping". In here we can map the input columns to the output columns.

I am writing this code in the function :

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

ComponentMetaData.Name = "Test";

ComponentMetaData.Description = "Test";

ComponentMetaData.ContactInfo = "Test";

IDTSInput90 input = ComponentMetaData.InputCollection.New();

input.Name = "Input";

input.Description = "Input Desc";

input.ExternalMetadataColumnCollection.IsUsed = true;

IDTSRuntimeConnection90 rtc = ComponentMetaData.RuntimeConnectionCollection.New();

rtc.Name = "Source";

rtc.Description = "Source Desc";

IDTSOutput90 output = ComponentMetaData.OutputCollection.New();

output.Name = "Output";

output.Description = "Output desc";

output.ExternalMetadataColumnCollection.IsUsed = true;

}

I am not able to connect(link) the input to the output ports. I am able to add new output columns in "Input and Output Properties" tab. The output ports which i am adding in this tab are not visible in "Column Mapping" tab. Please let me know if i am missing something in the component properties which will enable the linking of the input to output ports.

Thanks,

Vipul

|||

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

|||

Vipul123 wrote:

TGnat,

Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.

Thanks

Vipul

So you want to be able to reuse functionality? Currently the only way to do this is to write your own component (very difficult) or good old copy-and-paste (very easy). I know which I would choose.

Incidentally, there is functionality probably coming in Vnext that would allow you to do exactly what you want to do (i.e. build reusable collections of components) without writing code.

-Jamie

No comments:

Post a Comment