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

Serialize and deserialize to XML string

private static string XmlSerialize(T obj)

{

    var stream = new MemoryStream();

    var writer = new XmlTextWriter(stream, Encoding.ASCII);

    XmlSerializer ser = new XmlSerializer(typeof(T));

    ser.Serialize(writer, obj);

    stream = (MemoryStream)writer.BaseStream;

    var bytes = stream.ToArray();

    var encoding = new UTF8Encoding();

    string result = encoding.GetString(bytes);

    return result;

}

private static T XmlDeserialize(string xml)

{

    XmlSerializer xs = new XmlSerializer(typeof(T));

    UTF8Encoding encoding = new UTF8Encoding();

    Byte[] byteArray = encoding.GetBytes(xml);

    MemoryStream memoryStream = new MemoryStream(byteArray);

    XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);

    object obj = xs.Deserialize(memoryStream);

    return (T)obj;

}

Promo