Cannot import xml into varchar fields using SSIS -- sql server dts

forrestsjs
7/7/2006 2:33:01 PM

I want to import xml into varchar fields using SSIS, but the package designer
does not allow me...I get an error...

cannot convert between unicode and non-unicode string data

Changing varchar to nvarchar in the table fixes that problem, but of course
takes up more db space.

Is there a trick to getting around this or a way to describe the xml as
non-unicode so that SSIS does not see the source xml as unicode strings...or
will it always assume all xml is unicode?

I have the SSIS 2005 Wrox book but it does not elaborate much on this and
only has one example (importing into nvarchar fields which works but is not
great for large data warehouses). Any other good resources for this info?

Thanks,
Forrest

Hasan Quadri
7/12/2006 3:30:01 PM

Forrest,

You need to put the XML Source output into a Data Conversion transformation
and convert all the Unicode string data to ANSI (i.e., DT_WSTR->DT_STR,
DT_NTEXT->DT_TEXT). I'm not entirely sure why XML Source automatically uses
Unicode, even if you don't specify UTF-8 as an encoding, but that's the way
it is apparently.

Hasan

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Cannot import xml into varchar fields using SSIS