XML import error using SSIS: No maximum length was specified for the output column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

XML import error using SSIS: No maximum length was specified for the output column

We are using SQL 2008 standard and I am using SSIS to import a XML file. The xml schema file I am importing is listed at the end of this message.
Our client sends us a XML file every week with different data. Every time when I use SSIS to import this xml file, it will give me the below error for each text field and the destination dataflow will have a red x mark which would require me to remap the input columns and destination columns.
"Warning at {AE69ED7D-0F72-42B4-99AC-CF6CFD98B0F9} [XML data Source [1]]: No maximum length was specified for the output column "first-name" (6437) with external data type System.String. The SSIS Data Flow Task data type "DT_WSTR" with a length of 255 will be used."
For most of the fields, the default of 255 is ok. But there are 15 other fields which requires more than 255. In some cases, I need to define them as nvarchar(max). After I comply with these 15 larger-than-255 length requirement by making changes in the XML source using ‘Show Advanced Editor’, the import would work fine. The problem is when the next new xml file comes, I have to make the changes all over again. Plus to clear the red mark I mentioned above, I need to open every single destination dataflow (50 of them) and confirm the mapping.
Can anyone provide me with some guidance to these problem? How can I make SSIS to remember my changes. Any help on this is very much appreicated.
w
===============================================
XML schema file is shown as below.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="CheeseAndRice">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Institution">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="course">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="course-name" type="xs:string" />
<xs:element minOccurs="0" name="grad-class-year" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="course-start-stop">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="start-period" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="stop-period" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="start-month" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="stop-month" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" name="discipline" type="xs:string" />
<xs:element minOccurs="0" name="option" type="xs:string" />
<xs:element minOccurs="0" name="credit-hours" type="xs:decimal" />
<xs:element minOccurs="0" name="course-url" type="xs:string" />
<xs:element minOccurs="0" name="syllabi-url" type="xs:string" />
<xs:element minOccurs="0" name="prerequisites" type="xs:string" />
<xs:element minOccurs="0" name="syllabi" type="xs:string" />
<xs:element minOccurs="0" name="user">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="user-id" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="first-name" type="xs:string" />
<xs:element minOccurs="0" name="last-name" type="xs:string" />
<xs:element minOccurs="0" name="middle-name" type="xs:string" />
<xs:element minOccurs="0" name="write-access" type="xs:string" />
</xs:sequence>
<xs:attribute name="role" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="course-element">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="element-name" type="xs:string" />
<xs:element minOccurs="0" name="element-hours" type="xs:decimal" />
<xs:element minOccurs="0" name="element-weight" type="xs:unsignedByte" />
</xs:sequence>
<xs:attribute name="category" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="session">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="session-name" type="xs:string" />
<xs:element minOccurs="0" name="contact-hours" type="xs:decimal" />
<xs:element minOccurs="0" name="resources" type="xs:string" />
<xs:element minOccurs="0" name="objectives" type="xs:string" />
<xs:element minOccurs="0" name="notes" type="xs:string" />
<xs:element minOccurs="0" name="comments" type="xs:string" />
<xs:element minOccurs="0" maxOccurs="unbounded" name="user">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="user-id" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="first-name" type="xs:string" />
<xs:element minOccurs="0" name="last-name" type="xs:string" />
<xs:element minOccurs="0" name="middle-name" type="xs:string" />
<xs:element minOccurs="0" name="email" type="xs:string" />
<xs:element minOccurs="0" name="write-access" type="xs:string" />
</xs:sequence>
<xs:attribute name="role" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="session-element">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="element-name" type="xs:string" />
<xs:element minOccurs="0" name="element-weight" type="xs:unsignedByte" />
</xs:sequence>
<xs:attribute name="category" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="id" type="xs:unsignedShort" use="optional" />
<xs:attribute name="name" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Did you check the source table data type to match the same?
Is the source file content is similar every week or any changes to existing format?
The error you see is because XML uses DT_WSTR datatype which stores characters as UNICODE. Also respond to below Qs:
Does it have a Data Flow on the Control Flow tab?
If so, what elements do you have on your Data Flow?
Do you have an XML Data Source?
Is its output connected to anything?

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |