I was working on a SSIS package in SQL 2014 which was working there without any issues. Due to few factors we need to downgrade to work in SQL Server 2008 R2 for few months. I developed the packages in SQL 2008 BIDS and deployed. No changes done to any of the database objects as I thought I’m going to use the same in my package as well.
When I ran the SSIS package for the first time I got the below error.
[SSIS.Pipeline] Error: component "OLE DB Source 1" (16) failed the pre-execute phase and returned error code 0xC02092B4.
I started troubleshooting the package multiple times and found all perfect. If you notice it was throwing error on the data source so When I execute the stored procedures from SSMS even it worked perfectly fine returning all the data. After lot of debugging I found it’s failed because it returned number of rows affected to destination.. weired!!!
SET NOCOUNT ON;
on top of the stored procedures. Now the package got succeeded.
I was really laughing ! One thing I learnt is that dont return unnecessary data (no header messages too) to package which includes your PRINT statement in the procedure that might cause this.