Tuesday, 28 October 2014

SSIS - find element in table, use its key and insert to another table

I have database with following 3 tables:
·         Customers.CustomerId is integer and PK
·         Addresses.AddressId is integer and PK
·         CustomerAddresses.Customer_CustomerId is integer and FK to Customers
·         CustomerAddresses.Address_AddressId is integer and FK to Addresses
·         Customers.EntityId is GUID
·         Addresses.EntityId is GUID

I use SQL Server Integration Services.
I have also XML file with pairs of addressEntityID and customerEntityId. These are are EntityIDs (GUID), that need to be mapped to IDs (integer) and inserted into CustomerAddresses.

How to map this relationship and import to CustomerAddresses?
1.  In Data Flow drop XML source
2.  Add Derived column, map values from XML (from string to GUID)

3.  Add Lookup, connect to Addresses table, create JOIN from derived AddressGuid to EntityId. AddressId is output.
4.  Add another Lookup and do the same for Customers table
On "Specify how to handle rows with no matching entries" choose "Redirect rows to error output".
5.  Add DB destination, map values from Lookup to AddressId and CustomerId

Complete Data Flow

No comments:

Post a Comment