SSIS - query another row in current data pipeline for a value

Question

SSIS - I have data loaded and various transformations on the data complete, the problem is there is a parent/child relationship managed in the data - best explained by an example

each row has (column names are made up) row_key parent_row_key row_name parent_row_name

some rows have row_key == parent_row_key (their own parent) some rows relate to another row (row 25 is the parent to row 44 for example).

In this case, row 25 is parent to row 44. I need to put row 25's row_name in row 44's parent_row_name. How do I query the data in the pipeline for the value?

thanks.

Answer 1

Can you not just split the data using a multicast and then do a merge-join against itself?

Answer 2

You could write your data to a temp table in your database, a raw file destination or recordset destination (depending on the size of your dataset). Then you could run through you data again and query your temp data and find the correct parent.

Answer 3

Sounds like the way Macola (Exact Software) handles transactions. In their situation, a transaction is added to a table and it includes the reference to the original transaction in the row - for example, if the original transaction was a purchase in january and there is a payment in febrary, the february payment will include the transaction number from the january payment in a column refering to the parent. Since there is no foreign key relationship, each initial transaction is given a guid that can move between ledgers across the system.