Slow performance for package with XML destination column

Question

Over the past few months, I have made several SSIS packages to transfer data from an old database to a SQL Server database. It usually takes 10-20 minutes to process about 5 million records, depending on the transformation.

The problem I encountered with one of my packages is very poor performance, because one of the columns in my destination is a SQL Server XML data type.

Data comes in like this: 5 A script creates a Unicode string like this: <XmlData><Value>5</Value></XmlData> Destination is simply a column with XML data type 

It is very slow. Any advice? I did an SQL trace and noticed that behind the scene, SSIS performs a conversion on each line before inserting:

  declare @p as xml set @p=convert(xml,N'<XmlData><Value>5</Value></XmlData>') 

Answer 1

Try using a temporary table to store the resulting 5 million records without XML conversion, and then use SQL Server itself to move them from tempDB to the final destination:

 INSERT INTO final_destination (...) SELECT cast(N'<XmlData><Value>5</Value></XmlData>' AS XML) AS batch_converted_xml, col1, col2, colX FROM #tempTable 

If 5,000,000 revolutions is too much data for one batch, you can do this in small batches (100k lines should work like a charm).

The record captured by the profiler looks like an OleDB conversion with one command per line.