Combining two tables with SSIS into one destination table

Question

Hi all, I am new to SSIS, so please bear with me.

I created an Integration Services Project for SQL Server 2008 to import data from an old db to a new one. One of the things I need to do is import data from two old source tables into one new destination table.

What is the best way to do this?

I can easily see the results I want with a simple inner join query using tsql, but am not having any luck using the SSIS package. My current approach is a three step process:

1. Add OLE DB Source component that pulls all columns from my first source table
2. Add a Lookup component, which is the next step after my OLE DB Source component. In this I query the second source table 'using the results of a sql query' that returns no nulls, then drag the foreign key id from the 'available input columns' to the primary key in the available lookup columns. I also check the checkboxes in 'available input columns' to add 2 more columns.
3. Add OLE DB Destination, pointed to my destination table.

This process fails at the first step, not at the lookup step, and fails with the error "Row yielded no match during lookup". The foreign key cannot be null, and obviously the primary key can't either. I used a SQL statement in step to so I could make sure I don't get any null date values in the columns (there were a few) but I am still getting the error. If I output the first step failure path to a Flat File Destination, I get an empty CSV (watching in debug mode says ~600k records go into the flat file).

I am pretty stumped at this point and this seems like it should be super easy task. I have scoured the web for answers, and found this link that sounds like the same exact problem I am having, but changing the cache setting didn't help.

Help appreciated!

Answer 1

It sounds like you have a mismatch in the lookup. I'd hand run the queries and verify that tha both OLE DB SOurce has no null foriegn keys; and that each foreign-key matches something in the lookup table.

There is a simpler approach here. Use your inner join query you mentioned in the OLE DB SOurce. Don't use the table select, provide your SQL query with the join. This let's the SQL Server do all of the heavy lifting of the join and then SSIS can do the transferring.