Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave

|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi