I am trying to import XML data into a SQL Server 2005 table using SSIS. The schema I was given does not seem to work at all. However if I generate a schema in the xml source editor it seems to work although I am having problems with it. When the schema is generated it is assigning fields where there is only numeric data the type unsignedInt. The original schema actually used string for this field. The problem is that I cannot import the unsignedInt data type into a SQL table because it seems that no data type is compatible with unsignedInt. If I change the data type in the schema file to string the changes don't seem to appear in SSIS. For example in the data flow path the data type in the metadata still says DT_UI4. Does anyone have any experience with this?

Well there's not a 100% correlation between data types in most schemas, SQL Server and XML Schema are no exception. You can use BIGINT as the datatype in the SQL table and use a constraint to set the value to be between 0 and 4294967295.

--

Joe (Microsoft MVP - XML)

I tried that but I still get an error message saying "FieldName" can't be inserted because the conversion between types DT_UI8 abd DT_I8 is not supported.

Okay, in that case you will have to investigate if you can override the conversion in some way. Alternatively modify the schema so that it uses a signed value that can be converted.

--

Joe (Microsoft MVP - XML)