Merging data in SSIS

Question

Merging data in SSIS

RichardDouglas

SSCarpal Tunnel

Points: 4029
More actions
January 26, 2009 at 9:52 am

#155179

Hi all,
I'm pretty new to SSIS and don't know the best way (or any way to do it for that matter).
I have two data sources in my SSIS package, the first is a flat file that comes from an external supplier that contains a field that may be one of two different styles of reference (Lets call them RefA and RefB for ease of use).
The second source is a table that contains RefA and RefB so I can perform a lookup.
How can I link source one to source two leaving my reference column with RefA if it contains a RefA value and change it if it is RefB to RefA?
I've tried using Lookups and merged joins but I just get errors that make no sense, have been looking at this on and off for ages now. Unfortunately we don't have a SSIS expert on hand so could do with a pointer in the right direction!
Many thanks,
Rich
Hope this helps,
Rich
[p]
[/p]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eric SSCommitted Points: 1803 More actions · Answer 1

Can you post some more detail on the file and table structure plus a few rows of sample data as well as the desired result?

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 2

Hi Eric,

Thanks for your reply. I have actually soved the problem by just doing an update statement in an execute sql task. However I don't believe this is the optimal way of doing it.

The sql table just contains 2 attributes (RefA and RefB)

The text file has an attribute called Ref which is the first attribute of the file. The rest of the file just contains updated information from a third party that I have no problem with.

Ref will either contain a value from RefA (9 digit numeric) or RefB (8 character alphanumeric)

RefA is our main reference and I wish to change all instances of Ref to the RefA value if the record has a RefB value.

All records in the SQL table will contain a RefA value but not all of them will contain a RefB value (RefB is a legacy system reference)

It might be more clear if I provide a generic version of the update statement so you can see via T-SQL what I am trying to achieve.

Update TF

SET TF.Ref = ST.RefA

FROM TextFile TF

INNER JOIN SQLTable ST ON TF.Ref = ST.RefB

or where Ref has a RefB value change to RefA.

I would have thought that either a merge join or a lookup using a derived column would be the way to go but I just can't seem to get it right.

Rich

Hope this helps,
Rich

[p]
[/p]

Eric SSCommitted Points: 1803 More actions · Answer 3

You should be able to do this with a lookup and a derived column (see attached screenshot).

Send the file output into a lookup task and join File.Ref on SQLTable.RefB just like in the update and return SQLTable.RefA. Configure the error output to ignore failures (if there is no Ref to RefB match then Ref was RefA so you can keep that row).

So now coming out of the lookup you have File.Ref which I’m assuming is never null and SQLTable.RefA which will be null when File.Ref is actually RefA.

Send this into a derived column task that returns SQLTable.RefA when it is not null else return File.Ref. The expression would be something like this: isnull(SQLTable.RefA) ? File.Ref :SQLTable.RefA.

As far as performance if these are part numbers I’d assume the reference table is relatively large. You might want to test this with partial caching on the lookup task.