none
Cannot load metadata of XML datatype in stored procedure (in SSIS 2008 R2 OLE DB Command component)

    Dotaz

  • There is an stored procedure which takes XML as metadata on input. Anyway when I try to call the procedure (inSSIS DataFlow OLEDB Command component) like exec sp_workOutData ?,?,?,? I receive following error : Operand type clash: int is incompatible with xml. It seems that SSIS  has problem with stored procedure metadata in case of XML, because when I call like exec sp_workOutData ?,?,DBnull,? SSIS normally loads rest of metadata with no problem. So what can I do to work with XML on input of stored procedure in SSIS OLEDBCommand componnet? Thanks a lot for reply! Tom

    Well to be honest - I know nothing is perfect so I googled for a thing and I've found a few workarounds but they differ in a direction of the way. There is possibility to insert fake select, which should help to sustract metadata (I've tried a few kinds of sugar notations but no success at all - my experienced colegue anyway later told me that's no point in that as it is Input only sp with no out parameters...). Now I have to say another thing was to SET FMTONLY OFF which helps somehow to substract metadata, but no way with XML problem. When I continued I've found I can use ScriptComponent - we're in DataFlow...but it means to write all thing by hand and to throw OLE DB Command compoonent away? I didn't find this solution elegant. So I hope to get some help here :) Thanks again...

    pondělí 9. srpna 2010 10:15

Odpovědi

  • OK, I've found this http://connect.microsoft.com/SQLServer/feedback/details/554514/error-with-xml-datatype-in-ole-db-commands-using-sqlncli10-1 . This is funny I've been sitting on this for kind of time and at the moment I posted it I found the sol = sounds for MSDN forum ;D  . Thanks anyway.  Tom

    PS: If something would change, or you know something special or new in this issue please post it here.

    "Using the tools provided in SQL 2008 SP1 (BIDS, SSIS), there are issues when using Ole DB Commands to call stored procedures.

    It seems that a parameter of type XML for a stored procedure cannot be parsed and used properly. This was working properly in the SQLNCLI.1 version of the provider.

    Note that if I edit manually the package and use SQLNCLI.1 instead (ie the 2005 version of the provider), it works provided the backward compatibility pack is installed of course.

    Greg_V"

    WORKAROUND:

    "In my SSIS package, I too have an XML parameter for a stored procedure. Although, in my case, my XML will be pretty small. I updated the XML parameter to be NVARCHAR(MAX) and made sure the data that flowed to this parameter was also NVARCHAR(MAX). Inside my stored procedure, I CAST the NVARCHAR(MAX) back to XML. I hope this helps anyone else experiencing this same issue.

    -Jason"

     

     

    • Označen jako odpověď tiwigi pondělí 9. srpna 2010 14:20
    • Zrušeno označení jako odpověď tiwigi pondělí 9. srpna 2010 14:26
    • Označen jako odpověď KFL-MSMicrosoft employee, Owner neděle 20. února 2011 11:52
    pondělí 9. srpna 2010 14:20

Všechny reakce

  • OK, I've found this http://connect.microsoft.com/SQLServer/feedback/details/554514/error-with-xml-datatype-in-ole-db-commands-using-sqlncli10-1 . This is funny I've been sitting on this for kind of time and at the moment I posted it I found the sol = sounds for MSDN forum ;D  . Thanks anyway.  Tom

    PS: If something would change, or you know something special or new in this issue please post it here.

    "Using the tools provided in SQL 2008 SP1 (BIDS, SSIS), there are issues when using Ole DB Commands to call stored procedures.

    It seems that a parameter of type XML for a stored procedure cannot be parsed and used properly. This was working properly in the SQLNCLI.1 version of the provider.

    Note that if I edit manually the package and use SQLNCLI.1 instead (ie the 2005 version of the provider), it works provided the backward compatibility pack is installed of course.

    Greg_V"

    WORKAROUND:

    "In my SSIS package, I too have an XML parameter for a stored procedure. Although, in my case, my XML will be pretty small. I updated the XML parameter to be NVARCHAR(MAX) and made sure the data that flowed to this parameter was also NVARCHAR(MAX). Inside my stored procedure, I CAST the NVARCHAR(MAX) back to XML. I hope this helps anyone else experiencing this same issue.

    -Jason"

     

     

    • Označen jako odpověď tiwigi pondělí 9. srpna 2010 14:20
    • Zrušeno označení jako odpověď tiwigi pondělí 9. srpna 2010 14:26
    • Označen jako odpověď KFL-MSMicrosoft employee, Owner neděle 20. února 2011 11:52
    pondělí 9. srpna 2010 14:20
  • Jason,

    you are asking at Czech local forum and you cannot suppose answer in English... Maybe somebody will read and answer but as you see it cannot be as fast as if you asked your question in appropriate English forum, e.g. here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads or in other SQL oriented English forum.

    čtvrtek 11. listopadu 2010 14:18