SQL Server Integration Services (SSIS) Exporting From a Stored Procedure in SQL 2005

This is just a post in my continued efforts to publicize all errors that I find in hopes that it will save somebody else a few moments.

I was using the SQL 2005 Management Studio to export the results of a Stored Procedure to a Flat File. This stored procedures made changes to other tables (Deletes, Inserts and Updates) and returned one result set.

I got the followin errors when trying to export this.

- Pre-execute (Error) Messages
Error 0xc02092b4: Data Flow Task: A rowset based on the SQL command was not returned by the OLE DB provider.(SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component “Source – Query” (1) failed the pre-execute phase and returned error code 0xC02092B4.(SQL Server Import and Export Wizard)

It seems that the error may have been caused by column headers not getting returned properly or something related to that. I am unsure exactly what the real error was.

The problem seems to have to do with what you use to export the data. The “SQL Native Client” as well as “Microsoft OLE DB Provider for SQL Server” produced the same error above.

I had to use the “.Net Framework Data Provider for SqlServer” in order to get the data exported. This seems like a major problem of SSIS. Although there are workarounds, as it stands right now I was happier with DTS.

0 thoughts on “SQL Server Integration Services (SSIS) Exporting From a Stored Procedure in SQL 2005”

Leave a Reply

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

− 1 = two

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>