SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

BIDS 2008 does not support XML datatype in OLE DB Command

on January 23, 2012
Senario Overview:
We have some SP which expect some XML Type input parameters and also return XML Type parameter.
Having a requirement to insert data in bulk to Database we planned to use SSIS.
Working on the package, I found that SSIS 2008 doesn’t supports XML datatypes in OLE DB Command.
Error Details:
Operand type clash: int is incompatible with xml
Operand type clash: int is incompatible with xml
Googled.. and found that the issue has been solved in latest releases .. 🙂
But, No luck after installing the latest patch.
Found the following work around:
1. Create a new SP and use NVARCHAR(MAX) instead of Input XML Datatypes and call this SP from SSIS ( We could have modified the original though, but preferred avoiding dependencies issues)
2. Call the main SP from the new SP after type casting to XML
Now new issue:
As we have one XML Output datatype . It still went on giving error even after casting to NVARCHAR(MAX)
Finally, NVARCHAR(4000) worked fine.
Not a robust solution but good enuf to full fill our requirements 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: