I have had a dataflow set up to transfer some snapshot data from one database to another that has been working fine. However, I got a task of adding additional data to the dataset as well as back-filling my stored snapshot data as well. Obviously I warned my managers this was not a good idea as the backfilled snapshot data will not be correct. Alas, I still had to do this.
To make this task quick I figured I would just reuse my already existing dataflow and I would just modify the stored procedure that returns the recordset temporarily to get the old data.
My original query in the procedure was just simple select statement but my temporary query had to use a table variable. I had everything set up and I was ready to run my dataflow. However, I got the "A rowset based on the SQL command was not returned by the OLE DB provider" error. First I thought that perhaps SSIS has a problem with my columns not having exactly the same datatypes as the original query. So I made sure that the temporary table uses the exact same datatypes as the original query. I still got the same error.
I did a little searching on the Internet and I found the very simple solution: put SET NOCOUNT ON at the beginning of my stored procedure. Sure enough everything was fine after that.
I did a couple of tests and it seems like that while the SET NOCOUNT ON statement in the beginning of stored procedures is always a good idea, in SSIS if you use a table variable you MUST have it.