I have a data flow to migrate data from an old database to a new one. The old design had all the data and historical information (changes) stored in a single table with a "version" (incrementing integer) against the row.
The new design has two tables, one for the "current" state of the data and an audit (or history) table which records changes using a trigger. Therefore only one row exists for the "current" data and there are many history rows.
In my SSIS package I am using the following components to copy the current data to one table but then send all the data to the audit table.
The Multicast is used to split the stream of data and the Conditional Split identifies the "current" row and sends that to the Order table (the table is not actually called Order, before anyone comments about using a reserved word for a table name).
I created this flow because I could not see a way to use the Conditional Split to send all the data to the Audit destination and only the current row to the other.
I'm assuming that creating all the duplicate data and then discarding it to a Trash Destination is not very efficient and since I have about 52m rows to migrate I'm worried the transformation is going to take days.
Is there a better (more efficient) way to achieve the data split?
Note on data: I have applied a
row_number() to the data which allows me to identify the "current" row as number 1, all the rows including "current" need to go to the Audit table destination.
EDIT: I have found an alternative to the Multicast and Conditional Split suggested by this blog post from SSIS Junkie: Multiple outputs from a synchronous script transform
It uses a Script Component to send data to one or more output. I'm trying that method to see if it's any faster but after seeing Kenneth's answer and suggestion about removing the Trash Destination I'm not sure it will be.