SSIS – Avoid OLE DB Command

I decided to do a little performance testing on some common SSIS “Tricks” that increase speed and effectiveness of SSIS packages.  I wanted to define to what extent just one simple change could make to a package. 

So in this test I decided to show the difference in using a staging table vs. using the OLE DB Command in a SSIS package that uses a change data capture technique.  Many people have the impression that landing data in a staging table is bad for some reason, but in almost all cases I have found that it’s worth using a staging table for your changed data vs. using the OLE DB Command to update your data.  Once you’ve landed your data in a staging table simply us a Execute SQL Task to update the destination table and truncate the staging table after the update completes.

Sounds like your doing essentially the same thing doesn’t it?  Well, yes and no.  The end result is the same but the speed at which it gets there drastically different.  I updated 74,575 rows and here is the performance difference.

00:00:11.887 with staging table
02:02:30.393 with OLE DB Command

So, 12 seconds using a staging table and a little more than 2 hours with the OLE DB Command!  I couldn’t believe that simple change made that much of a difference.  So I ran them both again and got essentially the same result.

Here’s the reason why.  The OLE DB Command runs insert, update or delete statements for each row, while the Execute SQL Task does a Bulk Insert in this instance.  That means every single row that goes through your package would have an insert statement run when it gets to an OLE DB Command.

So if you know you are dealing with more than just a couple hundred rows per run then I would highly suggest using a staging table vs. the OLE DB Command.