'How to migrate this Many-to-Many table using an SSIS package?

I have a Many-to-Many table that I need to migrate. The old Many-To-Many table has two INT columns that are the primary keys of the related tables. The new Many-To-Many table has two GUID columns that are the primary keys of the related tables. The GUID column names are StreetId and StreetCategoryId.

I am having an issue with the StreetId GUID column. This column references the Street table, which has the following columns: GUID StreetId, NVARCHAR Street and GUID DrawingId. The old Street table has the same columns, except StreetId and DrawingId are of INT type.

I am trying to migrate the Many-to-Many table using an SSIS Data Flow. The logic is as follows: 1. OLE DB Source that uses the old Many-to-Many StreetCategory_Streets table. 2. Lookup that uses the old Streets table. StreetCategory_Streets.StreetId is linked to Streets.StreetId. Lookup Match Output. 3. Lookup that uses the new Streets table. This is where the challenge arises. What Available Input Column(s) can I use to link to Available Lookup Column(s)? I cannot use StreetCategory_Streets.StreetId because it is of INT type and the Streets.StreetId in the new table is of GUID type.

When I try to migrate the data from the old Many-To-Many table to the new Many-To-Many table, I receive an error message regarding a duplicate record. The reason is that the Street and DrawingId columns can have duplicate values. Only StreetId is unique. However, I cannot link it to the old StreetId column due to INT vs. GUID. How can I migrate this data?

sql-servervisual-studiossismany-to-manydata-migration


Solution 1:[1]

The following is my solution:

  1. Create a backup table of the original table to copy: select * into tableBackup from tableOriginal

  2. Add a temporary column to the backup table and the receiving table: Add a column of INT type to both tableBackup and tableReceiving

  3. Copy the unique values of an existing column to the temporary column in the backup table: update tableBackup set tempColumn = uniqueIDColumn

  4. Copy the values of the backup table to the receiving table using an SSIS Data Flow. Make sure to map the temporary columns with the unique values in both tables: Map tableBackup.tempColumn to tableReceiving.tempColumn

  5. Delete the temporary column in both tables.

In this scenario, the only unique values in the Street table were in the ID column. However, the StreetId column could not be mapped in the old and new tables because they were of different types, INT and GUID.

Sources

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

Source: Stack Overflow

Solution Source
Solution 1

Related Questions