How to map parent child table using SSIS dataflow

Question

Hi,
I am using Data Flow to get Customer Details from one source table and need to dump the details into a destination i.e another database table. But for some columns of the source table, I need to insert them into another table with the parent id used as foreign key that was inserted in the previous table. Below is the example:

SOURCE
Client:
CID FName LName Email
11 ABC DEF abc@def.com
22 GHI JKL ghi@jkl .com
33 MNO PQR mno@pqr.com

DESTINATION
Customer:
CID FNAME LNAME
1 ABC DEF
2 GHI JKL
3 MNO PQR

Contact:
ID CID EMAIL
1 1 abc@def.com
2 2 ghi@jkl .com
3 3 mno@pqr.com

The issue I am facing is that I am not able to get the CID from the Customer table that is getting inserted through a data flow in SSIS. How can I get the ids of the Customer table and use it to enter data into the Contact table?

Thanks

Answer 1

Hi @abhishek ,

You can use a Lookup Transformation on your data flow task to get the CID:

lookup-transformation

Also you may try with Merge Join Transformation in SSIS to insert the data.

You may also refer watch to see if it could give you some idea.

In addition, I think it will be much easier to meet your need with t-sql via SSMS.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October