Search code examples
ssisforeign-key-relationship

SSIS import using foreign key data?

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.

Solution

  • 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.