SSIS - lookup surrogate key for parent

Question

I have a table in my source DB that is self referencing

|BusinessID|...|ParentID|

This table is modeled in the DW as |SurrogateID|BusinessID|ParentID|

First question is, should the ParentID in the DW reference the surrogate id or the business id. My idea is that it should reference the surrogate id.

Then my problem occurs, in my dataflow task of SSIS, how can I lookup the surrogate key of the parent?

If I insert all rows where ParentID is null first and then the ones that are not null I solve part of the problem.

But I still have to lookup the rows that may reference a parent that is also a child.

I.e. I do have to make sure that the parents are loaded first into the DB to be able to use the lookup transformation.

Do I have to resolve to a for-each with sorted input?

Answer 1

One trick I've used in this situation is to load the rows without the ParentID. I then used another data flow to create an update script based on the source data and the loaded data, then used a SQL task to run the created update script. It won't win prizes for elegance, but it does work.