SSIS Error using a stored proc as source

SSIS is a great tool to move data around.  Sometimes it’s easier to use a stored proc to pull together some data before you do stuff with it.

To use a stored procedure you can use an OLE DB source (in a Data Flow task) SQL command.

Works great, but if it doesn’t and you get the error code 0xC02092B4, you will have to add to your stored procedure.

Just add the following 2 lines to the top of your stored procedure:

SET NOCOUNT ON
SET FMTONLY OFF

This will clear up the metadata issues with SSIS trying to understand what your stored procedures outputs.

Advertisements