SSIS package reports ‘metadata could not be determined’ after upgrade to Denali CTP3
While upgrading some of our SSIS packages to SQL Server Code-Named Denali CTP3, we got some errors in data flow tasks that have an OLE DB Command in them which call a stored procedure:
Error: 0xC0202009 at <Data Flow task name>, <OLE DB Command name> : 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
Description: “The metadata could not be determined because statement ‘<sql statement>’ in procedure ‘<procedure name>’ uses a temp table.”.
The OLE DB Command function tries to use one of the new programmability enhancements in Denali CTP3 to determine the format of the response of the stored procedure. This normally works very well, but in our case the stored procedure uses temp tables which causes the error above. As Aaron Bertrand already discovered in his blog post a while back, the new dynamic management function sys.dm_exec_describe_first_result does not support temp tables!
To get around this problem, we can simply fool SSIS by adding ‘WITH RESULT SETS NONE’ to the execute statement in the SQL command. This new option for the EXECUTE statement in Denali CTP3 guarantees that the execute statement will not return any results. If any results are returned the batch is aborted. But because we use stored procedures that doe not output any result sets, that is not a problem.
So instead of using:
EXECUTE ? = dbo.PROCEDURENAME ?, ?
We now use:
EXECUTE ? = dbo.PROCEDURENAME ?, ? WITH RESULT SETS NONE
With this addition SSIS does not expect any results from the stored procedure and the OLE DB command task executes successfully!
I haven’t tried yet but I think you can use the WITH RESULT SETS option as well to guide SSIS to the right output format of your stored procedures! When I find out I’ll be sure to let you know!
For more information on the new programmability enhancements in SQL Denali CTP3, click here.