The native SSIS functionality currently prevent you from performing a batch update from a Data Flow Task without staging the data. If you wish to update records from within the Data Flow Task, you’re going to have to use the OLE DB Command transform, which is very slow. If you’re update more than just a few records, I’d highly recommend first staging the data and then using an Execute SQL Task to perform the batch update.

Or you could just use the Task Factory Update Batch Transform from Pragmatic Works and avoid staging the data at all or having to use the performance-devouring OLE DB Command transform.

The Update Batch transform included with Task Factory utilizes an ADO.Net connection to perform a batch update of a SQL Server table quickly and efficiently. Configuring the Update Batch transform is very easy.

After dragging the Update Batch transform into your Data Flow design area and connecting a Data Flow Path to it, you can then double-click the transform to open the Update Batch Transform editor.

First you’ll need to either create a new ADO.Net connection or select an existing connection. In my case, I’m going to select a previously existing ADO.Net connection, ADO NET AW.

Next I’ll select the Table I would like to update. After selecting the table, I need to specify which column(s) should be used as key values in determining if a record in the table is a match and meets the criteria for updating the record. I can either select a single column or multiple columns to create a composite key.

Lastly, any fields in the table that I do not wish to update should be marked as ignore. Fields that need to be updated should mapped to the corresponding field in the Destination Column.

Now we’re ready to update our records in batch without the need for staging. One of the neat advantages to using the Update Batch Transform from Task Factory is the ability to use this transform as a destination or a transformation. If I choose to do so, I can allow the records moving through the data flow to move on to another transformation or destination, as you can see here.

I’ve found the Update Batch Transform to be very useful when performing mass updates. I’ve seen tremendous performance gains in my SSIS packages since I no longer need to perform additional reads/writes to first stage the data and then perform the update.

Head to PragmaticWorks.com to download the free Task Factory trial and check out some of the other useful task and transformations included with Task Factory.