SSIS: Error: A rowset based on the SQL command was not returned by the OLE DB provider.

you must put set nocount on and set nocount off around your stored procedure body. SInce the rowcount returned by the stored procedure is returned before the actual resultset, SSIS attempts to read that in as the resultset, which is why it complains about an invalid resultset. set nocount on will supress the rowcount message and only return the resultset.