Package fails if xml element is an empty string

Question

I have a simple data flow task which takes the contents of an xml file and loads it into a SQL table.
One column is called FLT and on occasion this element will be empty ("") and the package will fail.
The executions report claims "the input string was not in the correct format"
The table column allows NULLs so I'm not sure what the issue is.
More importantly I'm not sure how to allow for this empty string/NULL

Answer 1

Hi @Jay ,

Here is a sample demo for you.

Please compare my sample XML/XSD pair vs. yours. This comparison should provide you an answer to the issue you are experiencing.

You need to change data type in the XSD from the xs:unsignedShort to the xs:string.
An empty space cannot be implicitly converted into a numeric data type.

XML

<?xml version="1.0"?>

<root>  
 <row id="1" city="Sidney" FLT_="650"/>  
 <row id="4" city="Canberra" FLT_=""/>  
</root>  

XSD

<?xml version="1.0"?>

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified"  
           xmlns:xs="http://www.w3.org/2001/XMLSchema">  
 <xs:element name="root">  
 <xs:complexType>  
 <xs:sequence>  
 <xs:element minOccurs="0" maxOccurs="unbounded" name="row">  
 <xs:complexType>  
 <xs:attribute name="id" type="xs:int" use="optional"/>  
 <xs:attribute name="city" use="optional">  
 <xs:simpleType>  
 <xs:restriction base="xs:string">  
 <xs:maxLength value="100"/>  
 </xs:restriction>  
 </xs:simpleType>  
 </xs:attribute>  
 <xs:attribute name="FLT_" type="xs:string" use="optional"/>  
 </xs:complexType>  
 </xs:element>  
 </xs:sequence>  
 </xs:complexType>  
 </xs:element>  
</xs:schema>  

Package execution screen shot

Answer 2

It has an XML source task and a SQL destination task.
One of the mapped columns in the XML is called FLT. If this element contains the value "123" it works perfectly, it if contains "" it fails.
The "all executions" report in SQL Server says the reason for this error is:
Other than the below I am not sure what else would be of value to include.