I ran into a similar issue using Linked Servers to reach a MySQL DB, but it was on SQL 2005. The issue traced back a driver issue where it was...

Look into using the Conditional Split Data Flow Transformation in your Data Flow task.

rs80 (3/17/2011)

---

Pretty cool stuff...

So I think what you're saying is that even though SSMS has the limitation of truncating the XML when showing the results in grid or text (what...

You need to provide the password when running the package from the command line.

From MSDN article "How To Run a Package" http://msdn.microsoft.com/en-us/library/ms138023.aspx:

If the package encryption level is EncryptSensitiveWithPassword or EncryptAllWithPassword,...

I think the forum thread you referenced is referring to client-driver data access issue...that has nothing to do with the internal representations of the XML or NVARCHAR(MAX) types within SQL...

rs80 (3/16/2011)

---

I went down the road of trying to convert the xml to nvarchar(max) by following some google links, but when I set the xml to nvarchar(max) some of the...

rs80 (3/16/2011)

---

I went down the road of trying to convert the xml to nvarchar(max) by following some google links, but when I set the xml to nvarchar(max) some of the...

OK, that's unfortunate. You're stuck either CONVERTing your XML to a Unicode string and pre-pending it with a declaration before it leaves the T-SQL context and delivering it to the...

Can you post the DDL for your tables, some DML to create test data and the query you're using now so we have a starting point?

Please have a look at...

OK, super-high-level design of an SSIS package that will do what you need to do...

1. Add a Data Flow Task to your Control Flow. Inside your Data Flow add a...

Since all XML is stored within SQL Server with utf-16 encoding the engine does not bother maintaining the XML declaration when you insert into an XML column (i.e. it drops...

Oh, a third thing...prefix all your string literals with N to denote they should be treated as Unicode strings (i.e. they contain 'N'ational characters).

XML in SQL Server only support UTF-16LE. Two things, change all instances of VARCHAR in your script to NVARCHAR and change utf-8 to utf-16 in your xml declaration.

Chances are it has to do with the ProtectionLevel of the SSIS package. When you run it through BIDS it is running as you so has access to your user-key....

Indeed 😀

---

Can you force them to be committed? ...no, that is indeed nonsense

Can you force a blocking processes to rollback to keep your server healthy until you...