CODEWITHSUNDEEP
ssis
Tschareck
Tschareck

Reputation: 4239

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

I have database with following 3 tables:

enter image description here

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?

I want to do something like, for each row, select CustomerId from Customers where EntityId = {0} and then use this value to insert. Can this be done?

And before you suggest, that I should use EntityId as PK, the reason is, that those are not provided for all data in database. Only data, that are imported, and come from another system, those have EntityId.

Upvotes: 0

Views: 372

Answers (1)

Tschareck
Tschareck

Reputation: 4239

  1. In Data Flow drop XML source
  2. Add Derived column, map values from XML (it is necessary to map from string to GUID)
  3. Add Lookup, connect to Addresses table, create JOIN from derived AddressGuid to EntityId. AddressId is output.

Lookup configuration

  1. Add another Lookup and do the same for Customers table
  2. Add DB destination, map values from Lookup to AddressId and CustomerId

Upvotes: 1

Related Questions