SSIS Error Code 0xC02092B4

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!!!

Solution:

I added

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*