Unable to retrieve destination column descriptions from the parameters of the SQL command

Question

I am trying to upgrade an SSIS package from 2008 to 2012 and getting the below error.

Error: 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 'EXEC master.dbo.xp_logevent @ErrorCode, @Message, error' in procedure 'DebugPrint' invokes an extended stored procedure.".

Error: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Basically, we have an OLE DB Command to call a stored procedure which call several (nested) stored procedures and one of it is DebugPrint which call master.dbo.xp_logevent. Any idea to fix it? It works in SSIS 2008.

Thanks

If you open up sql server management studio and directly call the stored procedure on the 2012, does it work? My assumption is that it returns the same error message — billinkc, Jul 09 '13 at 23:59
Interesting error, does this approach resolve the issue? " changed the connection strings for the databases in the connection manager to use the provider SQLOLEDB.1 (Microsoft OLE DB Provider for SQL Server). It was set to SQLNCLI10.1 (SQL Server Native Client 10.0)" via this [msdn](http://bit.ly/187dH7f) thread — billinkc, Jul 10 '13 at 00:49
Thank you for your suggestions. It did not work either. After a discussion in my team, we decided to remove the Debug sps because it is not neccessary in our case and speed up our upgrading process. I hope someday, we understand more about this bug. — hieu, Jul 10 '13 at 16:09
I understand. The other thing to check out if you have a non-production system that you can recreate the error on is whether a patch, either Service pack, CU or QFE would alleviate the issue. — billinkc, Jul 10 '13 at 16:12

score 1 · Answer 1 · answered Jun 10 '14 at 21:18

1

You could try using 'with result sets' when calling your proc and setting your metadata there.

Example :

EXEC dbo.proc  
WITH RESULT SETS (
ID INT
,Col2 VARCHAR)

answered Jun 10 '14 at 21:18

SDr6

21
3

score 1 · Answer 2 · edited Dec 11 '15 at 01:31

I came across a similar error.

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 '....' uses a temp table.".

Work around.

If SP uses a #table or ##table and it is used in the SP, then we need to specify the #table structure along with the EXEC.

The SP should be given along with the structure.

EXEC SP_TestTemp 1,2

it should be given like

EXEC SP_TestTemp 1,2 WITH RESULT SETS
(
(
id      int,
Marks   int
)
)

Note: the 'retain same connection = true' and 'validate external metadata' = false did not help/work here

Unable to retrieve destination column descriptions from the parameters of the SQL command

2 Answers2