If the execution of a stored procedure as an OLE DB data source fails in your data flow with the following errors:
Error: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: component “XYZ” (1) failed the pre-execute phase and returned error code 0xC02092B4.
Try the following corrections in your stored procedure:
1. Setting NOCOUNT ON
2. Removing PRINT, CAST and CONVERT statements from the stored procedure
3. If you use temp tables inside the SP use SET FMTONLY OFF command
Anything in your stored proc that does not return a proper record set will cause this error.