ssis Derive update

Question

Im trying to merger data together based upon a primary key that will be the same in two record sets.

I have the first record set with a primary key and 5 coloums of data and then I have a second record set with the primary key and 5 new colums. I want to be able to merge both record sets together so I can see one primary key and 10 coloums with any data that is not applicable just is left null.

Can anyone help please :)

Answer 1

Even though it's SSIS, I've had more luck writing updates and such using SQL code, and then just using a SQL Task with that code. It's easier than the Merge and Merge Join data flows.

Answer 2

http://msdn.microsoft.com/en-us/library/ms141029.aspx

Have you had a look at the how-to guide on MSDN? It's a bit flakey with detail but it should help point you in the right direction.

The most annoying part is the data must be 'sorted' before being merged.

http://www.mssqltips.com/tip.asp?tip=1322

MSSQL tips has a much more comprehensive guide on how to get the Merge Join working. Just substitute their data-source for whatever you may be using.

Good luck, hope you get it working.

Answer 3

Hi

Use the Merge Join component in SSIS use the left outer join in the join condition.

your primary key columns as join key.

Merge join needs sorting before merging so make sort property of both the components as true and select the sort key order.

thanks

prav