I have been working on converting our packages from SQL 2005 to SQL 2012. I went through the wizard and got all the packages converted, but now am running into an issue that has me stumped. On the packages that uses a stored procedure (with temp tables in the SP) are giving me a validation error on the data flow task. This is the error: _____________________________________________________________ Exception from HRESULT: 0xC020204A Error at Create Extract[OLE DB Source [193]]: 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 11.0" Hresult: 0x80004005: "The metadata could not be determined because statement 'select client_no, total_assets' in procedure 'usp_get_assets' uses a temp table.". Error at Create Extract [OLE DB Source [193]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ___________________________________________________________ This package ran fine in SQL 2005 and it ran fine. A lot of our packages use Stored Procs that have temp tables in them. Any help or pointers will be appreciated! Thanks,

I have not seen or heard of this error yet. What the stored proc looks like? I think as a workaround you would need to revert to using a real table that must exist at development time.Arthur My Blog

here is something that can help you: http://josvanduijn.wordpress.com/2011/08/04/ssis-package-reports-metadata-could-not-be-determined-after-upgrade-to-denali-ctp3/ Please mark the post as answered if it answers your question

here is something that can help you: http://josvanduijn.wordpress.com/2011/08/04/ssis-package-reports-metadata-could-not-be-determined-after-upgrade-to-denali-ctp3/ Please mark the post as answered if it answers your question

Thank you so much! I will try that out and post the results.

Thank you so much! I will try that out and post the results.

Solved! This was how it was done - the execute command for the stored proc was modified as follows: exec <stored_proc> with result sets ( (<column_name1> <data type>, <column_name2><data_type>, ....)) SSIS doesn't recognize the column information from a temp table in a SP unless defined. Thanks for your help!  

I will mark the last post by you as the answer because it referencing the feature that has solved the issue - WITH RESULT SETS.Arthur My Blog