asked on

Transfer data using SSIS. Convert between int and guid.

Hello,

I'm in the process of moving data from one database to another. Both are Microsoft sql 2005 databases. I planning on using SSIS to do this but have run into a problem. Since the source database uses int for its primary key and the destination database uses guid for its primary key how can I preserve relationships between tables.

I am fairly new to SSIS but feel comfortable with straight forward data transfer between tables. Any advice/help would be greatly appreciated.

Thanks for the help in advance.
Dean.

navalarya1982

can you share the query you are using for data transfer?

drimando

ASKER

Thanks for the response.

I actually don't have a query to share as this is where I need help. I'm looking for some guidance as to how to go from a database that uses OID to one that uses GUID and still maintain table relationships.

Example:

Source Database has Customer, Project and Contact Tables with the following fields:
Customer Table: OID, CustomerName, ContactID, ProjectID
Contact Table: OID, ContactName
Project Table: OID, ProjectName

Destination Database has Customer, Project and Contact Tables with the following fields:
Customer Table: GUID, CustomerName, ContactGUID, ProjectGUID
Contact Table: GUID, ContactName
Project Table: GUID, ProjectName

How do you move data between the databases and still maintain the corresponding links to the Contact and Project Tables?

Thanks.

ASKER CERTIFIED SOLUTION

drydenhogg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drimando

ASKER

Hi drydenhogg,

I thought of a solution along these lines before posting but wasn't sure if there was an easier way. I was hoping there would be...

Thanks for your help.