ssis Data Migration - Master Detail records with new surrogate keys

Question

Finally reached data migration part of my Project and now trying to move data from MySQL to SQL Server.

SQL Server has new schema (mapping is not always one to one).

I am trying to use SSIS for the conversion, which I started learning today morning.

We have customer and customer location table in MySQL and equivalent table in SQL Server. In SQL server all my tables now have surrogate key column (GUID) and I am creating the same in Script Component.

Also note that I do have a primary key in current mysql tables.

What I am looking for is how I can add child records to customer location table with newly created guid as parent key.

I see that SSIS have Foreach loop container, is this of any use here.

if not another possibility that I can think of is create two Data Flow Task and [somehow] just before the master data is sent to Destination Component [Table] on primary dataflow task , add a variable with newly created GUID and another with old PrimaryID, which will be used to create source for DataTask Flow for child records.

May be to simplyfy , this can also be done once datatask for master is complete and then datatask for child reads this master data and inserts child records from MySQL to SQL Server table. This would though mean that I have to load all my parent table records back into memory.

I know this is all too confusing and it is mainly because I am very confused :-(, to bear with me and if you want more information let me know.

I have been through may links that i found through google search but none of them really explains( or I was not able to uderstand) how the process is carried out.

Please advise

regards,

Mar

** Edit 1**

after further searching and refining key words i found this link in SO and going through it to see if it can be used in my scenario

How to load parent child data found in EDI 823 lockbox file using SSIS?

Answer 1

OK here is what I would do. Put the my sql data into staging tables in sql server that have identity columns set up and an extra column for the eventual GUID which will start out as null. Now your records have a primary key.

Next comes the sneaky trick. Pick a required field (we use last_name) and instead of the real data insert the value form the id field in the staging table. Now you havea record that has both the guid and the id in it. Update the guid field in the staging table by joing to it on the ID and the required field you picked out. Now update the last_name field with the real data.

To avoid the sneaky trick and if this is only a onetime upload, add a column to your tables that contains the staging table id. Again you can use this to get the guid for inserting to related tables. Then when you are done, drop the extra column.

You are aware that there are performance issues involved with using GUIDs? Make sure not to make them the clustered index (as the PK they will be by default unless you specify differntly) and use newsequentialid() to populate them. Why are you using GUIDs? If an identity would work, it is usually better to use it.