SSIS import using foreign key data?

Question

I have an old database (OldDB) with a table (let's call it Call) that I'm using SSIS (2008) and a new database (NewDB) with the following setup:

OldDB.Call has a column called Statuswhich currently is varchar(1) and holds values such as "C", "D", etc.
NewDB now maps all the possible statuses in its own table with a foreign key constraint so that OldDB.Call.Status is now NewDB.CallStatus.id An example of the data in the NewDB.Call.StatusID would be 1,2, 3 and so forth.
NewDB.CallStatus now has a column called Status which holds the actual nvarchar(1) value of A,B, C, etc.

I'm using SSIS to migrate the data. So far, I know I need to use a Sort transformation for each source and then a Merge Join transformation to map the new NewDB.Call.StatusID to the OldDB.Call.Status value. For whatever reason, it seems to start just fine but ends up grabbing other columns (like a description column, for example) and shoves the wrong kind of data in there. In short, it's not mapping the foreign key like it should.

I've found numerous examples on the web on how to do this (like this) but it seems like I'm missing some key, critical piece of information in order to understand what I'm doing because I keep borking it.

In a perfect world, a step-by-step would be great but a good and concise tutorial or explanation would be useful as well. In short, I need to know how to hook those two tables up and map the value in OldDB to the foreign key in the the NewDB and store that value in NewDB.CallStatus.

Two things. First: can you post some screen shots of your package so we can get a better idea of what you're missing. Without that we are just kind of guessing. Second: it's not generally considered good to have a column that's varchar(1) since it's not variable length. It's always 1. — Zane
The varchar(1) is legacy that we're doing away with, thankfully. Aaand I can't do a screenshot anymore because it was far faster to just do it as a join in sql script - we wanted to automate in SSIS but... it's already done so... Thanks though. — MetalPhoenix

Mike Honey Mike Honey · Accepted Answer · 2015-05-27T06:18:12

I would use the Lookup Transformation for this requirement.

Within the Lookup definition, the Connection would point to your NewDB.CallStatus (writing a SELECT is best practice, rather than just choosing the table - it caches the metadata). On the Columns pane, map Status to Status, and choose StatusID as a Lookup column.

Now your data flow will carry that added column downstream, and you can deliver it (typically using an OLE DB Destination).

Lookup's default mode is Full Cache which will be much faster and use much less Memory compared to a Sort & Merge solution.

SSIS import using foreign key data?

1 Answers