April 27, 2010 at 6:39 am
Hi there,
I have a strange problem in a new setup in which I am testing my SSIS packages in SSIS 2008 (ie with BIDS 9.0, connecting to a SQL Server 2008 machine).
I have an ole db command in a data flow that calls a stored procedure (the sqlcommand is 'EXEC sp_test_xml @aXML = ?')that expects a parameter of type [xml]. The problem I have is that under 2005 it works like a charm whereas under 2008, I have the following error when I save the ole db command:
Operand type clash: int is incompatible with xml.
And lower:
Unable to retrieve destination column descriptions from the parameters of the SQL command.
Is this something that anybody has encountered? If so, how can I avoid this issue?
Thanks in advance,
Greg
April 27, 2010 at 3:14 pm
My first thought was to verify the input parameters and make sure all the types are right. I think the input mapping for XML should be string. And the variable holding the XML should be string..
I'm wondering if the input parameter issue is causing the output problem..
CEWII
April 27, 2010 at 4:40 pm
Hi,
The input parameter is the issue with the sqlncli10.1 provider (the new one for sql 2008). If i use the one from 2005 (sqlncli.1) by manually editing the package's xml code, all works well. It looks like the provider is not able to properly resolve the XML sql datatype to the .NET type it tries to use. I'm not too sure what the problem is or if I'm supposed to modify my code somehow but my stored procedure could be modified to use a nvarchar parameter which works but I really want to use the XML datatype without having to do the conversion inside the sp itself (kinda like trying to use something for its purpose 😉 ).
I submitted a bug the CONNECT in case: https://connect.microsoft.com/SQLServer/feedback/details/554514/error-with-xml-datatype-in-ole-db-commands-using-sqlncli10-1?wa=wsignin1.0
Regards,
Greg
April 27, 2010 at 6:07 pm
I know this is a stupid question but I'm going to ask anyway.. Have you applied the latest service pack?
CEWII
April 27, 2010 at 9:12 pm
No worries 🙂
Yes the machine is a brand new SQL 2008 x64 with SP1 and Windows 2008 64 SP1. Mind you I've reproduced it on my 32 bit Vista with SQL 2008 and SP1 as well.
April 26, 2012 at 7:50 am
Did you ever solved this problem??
I have the same issue now.
My ssis package works fine in 2005 but when upgrade and run in 2008 i get the error
" Unable to retrieve destination column descriptions from the parameters of the SQL command.
"
April 26, 2012 at 11:26 am
Hi there,
No I haven't been able to solve it in an ideal way. My solution was to force the use of the old driver in SSIS 2008 (ie use the old provider), but to do that I had to modify the package's XML directly to use the same provider as the one in the 2005 version of my package.
Hope this helps
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply