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.
