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

Got something to add?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s