'how to to add linktable with table in ssis package?

My problem is that I need to add customer and its addresses from source table, where is address1, address2, address3 columns plus other columns related to customer (customer data). Some of the addresses might not contain data, so actually number of inserted address rows per customer depends on data. Address columns contain street, city, and country fields for the address. These addresses are already in the target table, which I can easily locate using lookup transformation using street, city, and country fields. These all should be converted in SSIS package to customer table and the link table to the addresses. The problem is that normally when I do ETL package, there is only one source and one sink. Now there should be 2 destinations, and also newly added id of the customer table should be added to the link table, which contains 2 fields: customerid (newly added row) and addressid (from the result of the lookup). I have no idea how this should be done, can you help me? Does it need C#-transformation or can I do it with normal transformations?

The main problem is that after the customer data is inserted, you got the customer id what is needed in the link table. How this id can be retrieved for the link table?

enter image description here



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source