SSIS Script Component's PostExecute not firing when no records exist

Question

SSIS 2005: I have a DataFlow task consisting of an IDataReader source getting zero or more rows from an Oracle data source and pushing those rows to a Script Component being used as a destination. My goal is to load these records into a DataSet, so I override the Script Component's PreExecute method to build the DataSet and the child DataTable that will hold the records. Then in the InputRow method I add a row to the DataSet, and in the PostExecute method I load the DataSet into a ReadWriteVariable.

My problem is that if there are zero rows returned from the IDataReader and I launch the package from C# code (using the Dts object model), the DataSet never gets set in the ReadWriteVariable. If I change the query so that rows are always returned to the IDataReader, the DataSet gets loaded into the variable, but if zero rows are returned the variable remains just a simple System.Object.

The frustrating part is that if I add in a few System.Windows.Forms.MessageBox.Show() calls to my PreExecute and PostExecute methods and run the package from VisualStudio, the messages appear. I've even gone so far as to inspect the variable in the script component after I set it to the DataSet and return the count of DataTables in a MessageBox. It works just fine in the designer every time.

Can someone explain why the PostExecute method appears not to be firing when I run the package programatically, whereas when I run it from the designer it works just fine? is this some optimization being done in the DataFlow task? Ideally, I would like to see an empty DataSet put into the variable if there are no records returned.

Thanks in advance.

Answer 1

The design environment uses a slightly different runtime than executing the package directly, so these types of difference are not terribly uncommon. You may need to move your logic to populate the DataSet into the ProcessInout method - you'll need to override the default implementation. In there, check to see if all the rows have been processed (see this post from Michael Entin on how to check - http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx) and then do all the final processing.

I've learned that doing final steps of processing in PostExecute isn't a good practice - so now I use it only for clean up and logging.