BIDS 2008 does not support XML datatype in OLE DB Command

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

Googled.. and found that the issue has been solved in latest releases .. 🙂

But, No luck after installing the latest patch.

Posted the ISSUE on MSDN Form:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/be51fb99-5c28-4b3e-8d4b-a02df6e72ca4

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 🙂

---