CODEWITHSUNDEEP
ssisforeign-key-relationship
user4593252
user4593252

Reputation: 3506

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:

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.

Upvotes: 0

Views: 1053

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

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.

Upvotes: 1

Related Questions