Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

SSIS data flow

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.

share|improve this question
    
The script component should indeed improve the performance. it reduces the amount data which frees up memory. –  Kenneth Jun 8 '11 at 14:31
add comment

1 Answer

up vote 2 down vote accepted

I see no glaring issue with that data flow. I always suggest to do as much work as possible in your source queries, so if you can create a dataset at the outset that allows you to populate both tables via a simple split, it would certainly use less memory. But things like that are not always possible depending on the data source and data format.

Also, the trash destination is good for development/debugging but does no good in production. Remove it. Let the 'Unwanted Data'expire at the split. SSIS can figure out the rest.

As long as you avoid blocking components (UNION, MERGE, etc.) there isn't any reason this process would take days. I frequently process many millions of rows in SSIS without issue. SSIS is only as slow as the person who designed the process.

Is it currently having performance issues?

share|improve this answer
    
Thanks, I didn't realise you could just leave the output from the Conditional Split disconnected. When I tested the package on a subset of the data (about 32m rows) it took 20 hours. That feels slow to me but it was reading data from one database and writing to another on the same server; so that may be an issue. –  Tony Jun 8 '11 at 8:18
    
That sounds slow as well. If this is a one time operation and 20hrs is acceptable, I wouldn't worry about it too much. If this is a repeating process, I would start looking to optimize. Number of rows doesn't tell me much. It will have more to do with the size of the data in the buffer, network latency, etc. –  Kenneth Jun 8 '11 at 14:30
1  
I agree, it was waaaaay to slow :) I had the network guys check out the box the package is running on and they found a network config error, so the data just could not get in and out of the box fast enough. I also changed the OLE DB source to be a RAW file (data dumped to the SISS box) so I'm only writing to the DB. Those two changes decreased the load time to 45mins! –  Tony Jun 8 '11 at 18:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.