SSIS with stored procedures and temporary tables

Posted: 21 October 2010 in Uncategorized
Tags: , ,

Recently I was working on a stored procedure to accomplish a complex task in SQL Server 2008. So I spend some time working on the procedure. The procedure uses some temp tables created in the procedure itself.

After finishing this procedure it was time to use the procedure in a SSIS package. So I open the dataflow task is will be implemented in. Enter the Exec “<stored procedure name>” command and want to save…

HRESULT: 0xC020204A

Error: ” Error at Combined Job [Get Data[1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 10.0” Hresult: 0x80004005 Description: “Invalid object name ‘<tablename>’.”.

Error at Combined Job [Get data[1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Additional information:
Exception from HRESULT: 0xc020204A (Microsoft.SqlServer.DTSPipelineWrap)

But then this error occurs. Very annoying,  but after doing some research the problem is easily solved. SSIS actually does not handle temporary tables how it should. So with an easy workaround you will be able to use the stored procedure in no time.

The solution is to fake the columns it returns so it will pick up the names from the procedure. So we add the following code to our procedure:

IF 1=2


The code will never become executed because of the IF 1=2 but the column names will get loaded and used as return columns. So you want to enter all the columns of the #tmp table in the code.

Instead of casting al the columns it is possible to select them from an existing table, and the properties will get loaded automatically. But if the table structure of the temp table is non-existent then the description above is the way to go.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s