I have a simple SSIS package, and I'd like to complicate it a little.
Right now, it executes a stored procedure in an OLE DB Source, and adds the rows returned from the stored procedure to the data flow. Then, for each row returned, it executes an OLE DB Command transform, executing a second stored procedure (in a second database), passing the columns from the source as parameters.
The second stored procedure performs a synchronization function, and I would like to log the grand total number of adds, deletes and updates. The "sync" stored procedure uses the OUTPUT clause of a MERGE statement to get this data and return it as a resultset.
I don't see a way to get this resultset out of the OLE DB Command transform. It does not allow me to add output columns.
Short of adding a Script Transform, is there a way for me to log the grand total of the add, delete and update columns?
This is not as straight forward as it ought to be. That or I need to go back to SSIS class.
The OLE DB Command component can't add new rows to the dataflow, as it's a synchronous component.
It also cannot add new columns to the data flow. That's the first thing that was non-intuitive. So you'll see in my source, I have added an ActionName column of type nvarchar(10)/string length of 10. You could add the column in a Derived Column Transformation prior to the OLE DB Command component if you so wish.
Since I can't add rows to the data flow, that means I'm only able to use an OUTPUT parameter for my proc instead of using the recordset it could generate. Perhaps your stored procedure only allows for one row to be altered at a time and this is ok but has a general code smell to me.
Table definition and set up
Stored Proc
Source Query
OLE DB Command setup
Results
References
Biml
Assuming you have the free BidsHelper the following Biml was used to generate this package.