flat xml file with Special character to sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

flat xml file with Special character to sql 2005

Author

Topic

g_r1212
Starting Member

4 Posts

Posted - 2011-08-22 : 18:28:46

hi,

I would like to know how to import flat xml file with Special character to sql 2005.

I have created a ssis package that import flat xml file to sql 2005 working fine, but when the xml flat file have rows with special charater data for sample " ñ or á "the ssis process display de following error message;

"Error: 0xC02090E7 at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to read the XML data.
Invalid character in the given encoding. Line 51, position 47."

Many thanks to any kind of help!
best regards,
Gilbert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-22 : 23:52:12

are you using unicode as data type? is code page used supporting these non english characters?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

g_r1212
Starting Member

4 Posts

Posted - 2011-08-23 : 11:47:37

visakh16,

I think so I have added a "derived column" with Data type String[DT STR] and code page 1252 in a dataflow. Is the name column. Need to support ñ and á for sample and I am getting the message;

"Error: 0xC02090E7 at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to read the XML data.
Invalid character in the given encoding. Line 51, position 47."

Many thanks to any kind of help!
best regards,
Gilbert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 12:03:26

try using DT_WSTR

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

g_r1212
Starting Member

4 Posts

Posted - 2011-08-23 : 14:03:08

I have tried using DT_WSTR and display the following error message;

Error at Data Flow Task [OLE DB Destination [9500]]: Columns "MISC_Referencia1_Nombre_D" and "MISC_Referencia1_Nombre" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB Destination" (9500)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

best regards,
Gilbert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 01:33:37

can you add a derived column transformation to do casting and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

g_r1212
Starting Member

4 Posts

Posted - 2011-09-01 : 12:24:04

Data type String[DT STR]

Dear visakh16

I have opened the Advance Editor for XML and I had set;

Unicode = 1252

Data type= "string [DT_STR]"

in the imput and ouput properties Tab

and

When I am trying to press ok button display the following error message;

TITLE: Microsoft Visual Studio
------------------------------

Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:

Error at Data Flow Task [XML Source [1]]: The SSIS Data Flow Task data type "DT_STR" on the external metadata column "MISC_Referencia1_Nombre" (14898) is not supported for the component "XML Source" (1).

Also,
I have opened the Advance Editor for XML and I had set "Unicode string [DT_WSTR]" Data type in the imput and ouput properties Tab

and display the following error message;

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Error: 0xC02090E7 at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to read the XML data. Invalid character in the given encoding. Line 51, position 47.
Error: 0xC0047019 at Data Flow Task, DTS.Pipeline: component "XML Source" (1) failed the prepare phase and returned error code 0xC02090E7.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (9500)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "AUTOS_Test.dtsx" finished: Success.

Many thanks to any kind of help!
best regards,
Gilbert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 00:42:21

try casting it using derived column stransformation after xml source

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources