SQL Server Performance

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

Discussion in 'SQL Server 2008 Integration Services' started by WingSzeto, Jan 7, 2011.

  1. WingSzeto Member

    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>
  2. satya Moderator

    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?

Share This Page