Hello all!
I have two MSSQL 2008 databases dbA and dbB, dbA contains master-detail tables pair: AMaster, ADetail. Corresponding it dbB also contains BMaster and BDetail. The only one difference betwee A and B is type of primary key. In source database (dbA) it is integer but in destination (dbB) it is uniqueidentifier.
Dear colleagues: how to describe dataflow in SSIS to convert this case? I need convert all fields, but replace it with new key type.
If you do not want to store the natural primary key in the destination how do you mange updates. Like if a record is changed in the source and you want to replicate a corresponding change in the destination table, how will you do that, I mean this not logically possible. Either you will have to keep the integer based natural key in the destination table or you will have to keep mapping table which stores old natural key and corresponding new key mapping info.
And UID is far bigger number and I don't think that converting integer to UID is a good option. If you really want to do it, do it this way. INT >> HEX STRING >> UID. Check more on UID here [http://msdn.microsoft.com/en-us/library/ms187942.aspx%5D