SSIS: Handling Identity Mapping When Migrating Data Between Databases

Hi,

I’m new to SSIS and have been assigned to migrate data from an old database (v1) to a newer one (v2). The newer database already contains data, and its tables use identity columns as primary keys.

The challenge is that there are multiple tables related by foreign keys to one master table. I plan to start with the master table — inserting records from the old master table into the new one. This will generate new identity values, which I then need to apply to the dependent (child) tables so that relationships remain consistent.

I was instructed to use minimal T-SQL and rely primarily on SSIS tools/components. However, so far, the only approach I’ve found is to:

Insert data into the new master table using a SQL query.

Capture the generated identity values along with their corresponding old IDs.

Store this mapping in a temporary or staging table for use when migrating related tables.

Is there a way to handle this mapping or identity synchronization purely using SSIS components, or at least with minimal SQL scripting?

Any guidance, best practices, or examples would be greatly appreciated.

Thank you!