Search code examples
sql-serverssisetllookup-tablesdata-integration

SSIS designer Visual Studio foreign keys integration

I need to integrate two similar databases into third DB3. DB3 is almost the same as DB1.

First database DB1:

Addresses table with: primary key AddressId

People table with: primary key PersonId , foreign key AddressId

Second database DB2:

It is pretty similar, but in other language

Data from DB1 to DB3 flows smoothly, table after table. For example I have 1000 records in DB3 table named Addresses from DB1 and 1000 records in table named People from DB1.

Let's suppose Person with number 30 in DB3 (after transfering from DB1) has the IdAddress number 20.

Address with number 40 in DB2 has the ID number 1040 in DB3 and the Person has ID number 30 in DB2 and 1030 in DB3.

While transferring table People from B2 to B3 we need to know the address ID is not 40 but 1040.

I'm trying to use lookup to find existing record, but I'm newbie in SSIS VS designer. Could you help me? How can I resolve this problem?

Solution

  • Suggestions

    You can do this using Lookup Transformation component as you mentioned, but first you have to:

    1. Select the basic information of each table that can distinguish each logical entity. Example if talking about Persons you can choose the Fullname+ Mothername + Date Of Birth , ...
    2. After selecting this attributes you have to add a Lookup Transformation
    3. Map thiese columns between Source and Lookup table
    4. Select the ID column (from Lookup table) as Output and rename the column to be NewID
    5. Select Ignore failure option to handle non matches situation
    6. After doing these steps, if the same person was inserted previously you will get the ID in NewID column, else NewID is NULL

    Additional Information