Help w/SSIS Conversion Error from XML

Discussion:

(too old to reply)

Jim Owen

2009-01-11 05:40:00 UTC

Hi,

I've been trying to use an SSIS project to acquire XML data and load it into
a table within SQLServer 2008. The project is returning an error whenever I
attempt to import a value defined as DT_UI8 or DT_UI2 into the table.

The specific error is:

Error at Data Flow Task [SQL Server Destination [546944]]: The column "id"
can't be inserted because the conversion between types DT_UI2 and DT_I4 is
not supported.

Error at Data Flow Task [SQL Server Destination [546944]]: The column
"Attribute_Id" can't be inserted because the conversion between types DT_UI8
and DT_NUMERIC is not supported.

I've seen several mentions of this type of error posted various places, but
I haven't found anything that leads to a solution.

Any ideas of how to handle this?

Thanks,
Jim

Todd C

2009-01-15 20:05:01 UTC

Permalink

Jim:
Do you have a traditional Data Flow task with XML Source and data pipeline?
Are you using a Data Conversion task that explicitly converts from one type
to another?
Since this is happening at the destination, sounds like a Data Conversion
can solve this.

If not. try switching to an OLE Db Destination instead of a SQL Destination.
As strange as it might sound, the OLE Db is a little better than the
proprietary SQL Destination.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by Jim Owen
Hi,
I've been trying to use an SSIS project to acquire XML data and load it into
a table within SQLServer 2008. The project is returning an error whenever I
attempt to import a value defined as DT_UI8 or DT_UI2 into the table.
Error at Data Flow Task [SQL Server Destination [546944]]: The column "id"
can't be inserted because the conversion between types DT_UI2 and DT_I4 is
not supported.
Error at Data Flow Task [SQL Server Destination [546944]]: The column
"Attribute_Id" can't be inserted because the conversion between types DT_UI8
and DT_NUMERIC is not supported.
I've seen several mentions of this type of error posted various places, but
I haven't found anything that leads to a solution.
Any ideas of how to handle this?
Thanks,
Jim

Jim Owen

2009-01-17 18:41:01 UTC

Permalink

Hi Todd,

I ended up handling this a little differently simply to get through the
initial process - I simply used an Excel spreadsheet as a destination. That
eliminated any difficulties with conversion of data since it is largely
ignored.

But to answer your question back to me, yes, I tried both a Data
Conversion task as well as the more simple case of XML Source directly into
the SQL Server destination.

I'll give a try to using the OLE DB destination - that might work and I
need to sort it out.

Thanks for your response and help,

- Jim

Post by Todd C
Do you have a traditional Data Flow task with XML Source and data pipeline?
Are you using a Data Conversion task that explicitly converts from one type
to another?
Since this is happening at the destination, sounds like a Data Conversion
can solve this.
If not. try switching to an OLE Db Destination instead of a SQL Destination.
As strange as it might sound, the OLE Db is a little better than the
proprietary SQL Destination.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]