Join Using a substring function

db_guy

Hi All,

I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.

Thanks

Phil Brammer

I would do the join in Transact-SQL first, if possible.

db_guy

I already have a SQL script (SP) that works fine, however what I am trying to achieve is get rid of this SP/view concept and replace with SSIS packages/tasks/transformations. Thanks for helping.

jwelch

You can use a Derived Column transform to do a substring. I would recommend that you use a Derived Column transform to get the stage data to match with your dimension table before doing the Lookup.

If you absolutely have to do the transformation on the dimension table data, you can override the SQL generated by the Lookup by checking the enable memory restriction option. This will negatively impact performance though. Or you could use a Merge Join (getting your data from the dimension via an OLE DB Source, run it through a Derived Column transform, thenMerge Join it to the stage data.

db_guy

That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.

Phil Brammer

db_guy wrote:

That Really helps, Thanks a lot, howevere another small thing, I have my datatypes different on the Dimesion table and the Stage table , its not allowing me to do a lookup, is there any workaround for this also If I end up writing sql queries inside this task then I might be better of using the same SP to load the fact, any suggestions on this from performance perspetive. Appreciate your help.

You can use a derived column to convert the input data type to match that of the column in the lookup table.

db_guy

Thanks for ur help Phil