In my Data Flow Task I am reading from a XML file that contains a field for social security number. My problem is that the XML Source is reading it as a numeric field so when a ssn contains leading zeros, like 067839378, the output is wrong. In my XSD I specify the field as string. Here's a sample of my XML file: <caseInfo> <caseNumber>103</caseNumber> <createdDate>20090729</createdDate> <lastName>RUDOLPH</lastName> <firstName>RED</firstName> <ssn>071610340</ssn> <dob>19761225</dob> <closedDate>20090727</closedDate></caseInfo> Here's the part of my XSD that defines the output: <xs:element name="caseInfo"> <xs:complexType> <xs:sequence> <xs:element name="caseNumber" type="xs:unsignedInt" /> <xs:element name="createdDate" type="xs:unsignedInt" /> <xs:element name="lastName" type="xs:string" /> <xs:element name="firstName" type="xs:string" /> <xs:element name="ssn" type="xs:string" /> <xs:element name="dob" type="xs:string" /> <xs:element name="closedDate" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element>

Excel does the same thing and it's a PAIN. If its something like SSN that has a fixed number of digits (9), then you can put in a Derived Column transform that pads in leading zeros: RIGHT("0000" + (DT_STR, 9, 1252)([SSN]), 9)Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Thanks Todd. Your suggestion worked nicely!

I have just experimented and concluded that you may have arrived to your issue after you had modified the XSD. I suspect you needed to do so because the XSD that was auto-generated by the XML source made the data type unsignedByte for the SSN column (this is expected because the field consists of all digits). You rightfully edited this element in XSD to have it as a string, but - here is the caveat: you also needed to either refresh the XML Source by right-clicking and choosing Advance Editor, Refresh button at the bottom or to drop and re-create your XML Source thereafter (otherwise the metadata is not going to be refreshed and would keep showing the original data type which was DT_IU1). Besides, one can inspect the metadata by double-clicking the link from the XML Source. Therefore to fix, you actually did not need the derived column, you could merely drop and re-build the XML Source using the new XSD. Moral is, IMHO, SSIS works as expected but lucks metadata auto-refresh on an event of XSD changes. Now I suspect once the data source will be refreshed one way or another the forced 0 left-padding may become an issue! Arthur My Blog