CODEWITHSUNDEEP
xmlsql-server-2005ssis
Jim Evans
Jim Evans

Reputation: 6505

SSIS 2005 truncating XML data on import?

I have an SSIS (2005) package that transforms some XML data and then imports it into SQL 2005. The column that the data goes to is nvarchar(max). The length of the data varies between 500 chars and 750 chars. When viewing the XML file that get created for import after the transform (XSLT) in Notepad ++ it is not truncated. the very next step in the package is the import to SQL Server. The data in the table after the import get truncated to about 450 or so chars. I know that in query analyzer you may not see all the data so I generated a script using SQL MGT Studio to script the table and the data and then viewed that in Notepad ++ and sure enough - the data had been truncated.

The SSIS package uses an XML Datasource to grab the transformed file. A Data Conversion (non-unicode to unicode for the nvarchar(max)) and an OLE DB Destination.

Upvotes: 1

Views: 1183

Answers (2)

SinisterPenguin
SinisterPenguin

Reputation: 1618

You can also tell the component to ignore truncation by double clicking the XML source editor, selecting Error Output & set truncation to "ignore failure".

I find this useful for elements that I'm not even going to use in my data flow, one less thing to go wrong.

Re-sizing the data is the better option if you want to use column in your data flow.

Upvotes: 0

Jim Evans
Jim Evans

Reputation: 6505

FYI - If anyone is interested I found the answer here - scroll down by the bottom of the article where he describes "Show Advanced Editor..." for the XML Source item. This solved my issue perfectly.

Upvotes: 1

Related Questions