Mar 31, 2012

The "component "" failed because error code 0x80131537 occurred, and the error row disposition on "output column "" at "output "" specifies failure on error. An error occurred on the specified object of the specified component.


In SSIS when we will use data source as XML Source or from any other component and transmit to any destination source, we may get error message like:

The "component "XML Source" failed because error code 0x80131537 occurred, and the error row disposition on "output column "" at "output "" specifies failure on error. An error occurred on the specified object of the specified component.

Cause: It is due to data type mismatching. In other word we can say that we are trying to stores string data into int column, long data in int column etc.
    
Solution:

In case of XML Source we have to correct its .xsd file.

Step 1: From error message find out following:

Error message:

[XML Source [210]] Error: The "component "XML Source" (210)" failed because error code 0x80131537 occurred, and the error row disposition on "output column "state" (1362)" at "output "address" (796)" specifies failure on error. An error occurred on the specified object of the specified component.

a. Component name: XML Source
b. Output name: state
c. Column name: address

Step 2:  In your XML file find out actual data where in XML tag = Column name (inside Output name tag) and find out it types. (I.e. is it String, Int or Boolean etc.?). For example:

1.

<address state="GA" city="Lawrenceville" zip="30043" street="331 Ashbourne Trail"/>

Here column state is type of string.

2.

<address>
< state>LA</ state >
</address>

Here column state is type of string.

Step 3: Now find out element name = Column name in your .xsd file and compare the type. For example:

1.

<xs:element minOccurs="0" name="address">
      <xs:complexType>
            <xs:attribute name="state" type="xs:int" use="optional"  />
            <xs:attribute name="city" type="xs:string" use="optional" />
            <xs:attribute name="zip" type="xs:unsignedShort" use="optional" />
            <xs:attribute name="street" type="xs:string" use="optional" />
      </xs:complexType>
</xs:element>

Here type of column state is int.

2.

<xs:element minOccurs="0" name="state" type="xs:int" />

Here type of column state is int.

Step 4: If it not matching then, write down more generic data type in .xsd file. For example xs:string can accept int, boolean etc. or xs:long can accept boolean, int etc. For example:

1.

<xs:element minOccurs="0" name="address">
      <xs:complexType>
            <xs:attribute name="state" type="xs:string" use="optional"  />
            <xs:attribute name="city" type="xs:string" use="optional" />
            <xs:attribute name="zip" type="xs:unsignedShort" use="optional" />
            <xs:attribute name="street" type="xs:string" use="optional" />
      </xs:complexType>
</xs:element>

Here type of column state is int.

2.

<xs:element minOccurs="0" name="state" type="xs:string" />

Note: Data true and false comes under string valid Boolean value is 0 and 1  

Step 4: Save the .xsd file. Right click on XML source and choose edit option. You may get following error message:


Click on Yes button then OK button.

Step 5: In all effected components right click on it and choose edit option. Select all invalid inputs and from Column mapping option select row combo box choose Map using column name and click on Apply button and then OK button.

  

3 comments:

  1. Perfect! Thanks to people like you to help us understand SSIS obscure error messages.

    ReplyDelete
  2. Anonymous7/22/2013

    Thanks for sharing. Good luck.

    ReplyDelete
  3. Thanks for sharing.. It really helps.. :)

    ReplyDelete