O'Reilly logo

Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services by Mike Davis, Devin Knight, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 25. Altering Rows with the OLE DB Command Transform

The OLE DB Command Transform is used to run a SQL statement for each row in the Data Flow. It sounds innocent enough, doesn't it? The reality is that the statement "for each row" should immediately make alarms go off in your head. This involves kicking off an update, insert, or delete statement for each row in an input stream.

To put this into perspective, imagine you are loading a product dimension table in your ETL process. Your predecessor decided it would be best to update and delete these rows using an OLE DB Command. The company you work for is a major department store, and the new spring clothing line is coming in. So, all the winter clothes are being marked down. This means you are going to get an update with a price reduction for all the winter clothes your company has in inventory at one time. Using the OLE DB Command Transform would mean that your package would be running several thousand update statements and your package would run for hours. A situation like that one is why we recommend you avoid using the OLE DB Command Transform.

So if we recommend not using the OLE DB Command Transform, what are your options? The best practice would be to insert all rows marked as updates into a staging table and then in the Control Flow use an Execute SQL Task to update the destination table. Why is this better than using the OLE DB Command Transform? The Execute SQL Task performs this operation in bulk versus the several ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required