OLE DB Command Transformation – Bottleneck for the Performance.

In many cases, Most of the time when we use SCD, OLEDB command transformation comes into the pictures.
Well as long as you have 100 – 10,000s records it doesn’t gives you any problems.
But the problem with this task is that it does row by row operation means if you have 10K rows are coming into its input, it’s going to pickup one row perform the operation or say SQL command you have in this task and then it will take another one. Well as it sound it is that lengthy.

In many of my project I found it bottleneck for the performance. And removing it makes my day.
Question here is what to use inserted of it –

In case of SCD –
Use OLEDB destination instead of update “OLE DB Command Transformation”. Insert rows into a temporary table and then use “Execute SQL Task” and do the update using your temporary table.

Remember to drop - create or truncate the temporary table after or just before the start of ETL execution.

Other cases -
Well same thing, use temporary tables, Execute SQL task to do the same.


Now where to use it – well when you have very less records for which you have to run OLEDB command or when you are rejecting and while rejection you might want to update some flag.

Thumb Rule here is Avoid this task when you have Bulk data.