Oledbcommand not updating
While designing a package last week with our consultant, Latha Chathri (twitter), I came across a need to call a stored procedure drom within a Data Flow Task.I have never needed to do this before and while the call is simple there are a few quirks that I found and would like to share with you.“Why does my data flow take so long to execute when I use an OLE DB Command component?“I need to update values in another table for every row in my data flow.” These are common questions I see when dealing with “slow” performance of an SSIS package.
Then, in the control flow, after the data flow successfully executes, you can issue an Execute SQL Task to perform a batch update.
) If you have a need to update data in another table, while still pushing data from a source to a destination, the OLE DB Command is often used because of the thought that it is part of the data flow, so why not use it. The trouble with the OLE DB Command component is that it executes whatever command you have provided for each and every row that passes through it.
The data flow is designed to operate on buffers of data, which contain by default around 10,000 rows.
For example, if you call Delete on a row on the Data Set, then when Update is called on the Data Adapter, the Delete Command of the Data Adapter will be called using the particular row in the Data Set.
Note: Keep in mind that this Update is different than a SQL UPDATE statement.
I'm getting ahead of myself, I explain more a little later in the blog. This component will call the procedure and pass in the parameters. How do I get the output from the procedure as well as the return value? Now, I want to show you one more thing before we go.