DataFlow Update?

paburgos

Hi,

I'm wondering if it is possible to make an update of a specifics rows in a database table using dataflow tasks

thanks

Phil Brammer


Well, sure, you can use the OLE DB Command component.

However, for every row passing through the OLE DB Command, the SQL contained within will get executed.

MatthewRoche

As Phil pointed out, the OLE DB Command transform is the way (the only way) to perform an update in the data flow, but it doesn't scale particularly well. A "better" (I put better in quotes because it's hard to make generalized judgment calls without more information than we have available here) way to do updates is to use a destination component to bulk insert (very fast) the records into a holding table. You can then use an Execute SQL task in your control flow after the data flow to perform a set-based batch UPDATE against the database. This tends to perform orders of magnitude better if you have large data volumes.

Lindsay

We have recently changed our data flow to update with an OLE DB command. It will update approx 3,000 rows. When I attempt to execute the entire package, it gets hung up on this data flow task. (The box turns yellow - when I look at the data flow itself, the boxes never turn yellow, and you do not see the row count.) We didn't think updating 3,000 rows would cause it to perform much slower. Do you think the OLE DB command is the cause I can execute that task alone and it will be done in mere seconds.

Phil Brammer

Yes. It's committing every row that passes through it. This is a very expensive component, and should be avoided in favor of Matthew's suggestion. Load your updates to a staging table, and then issue a one-time update SQL statement using that table as the source of the changes.

I personally avoid the OLE DB Command.

Lindsay

Phil, I loaded the data into a Temp table instead. However, it is still hanging on that Data Flow task. I have another Data Flow Task before that one in the Control flow, which is executing a stored procedure to get data from staging, and inserts it in our Data Mart. Do you have any other ideas as to what is causing it to hang

Rafael Salas

It would be good to know what is inside of the data flow that seems to hang, and on which step. Check that the SP that you use on that daflow runs fine in SSMS, just to make sure is not a DB related issue.

jwelch

If you use the Activity Monitor in Management Studio, you can see if another process is blocking your data flow.