I am using an ODBC source connected to the Hadoop system and read the column PONum with value 4400023488 of datatype Text_Stream DT_Text]. Data is converted into string [DT_WSTR] using a data conversion transformation and then inserted into SQL Server using an OLE DB Destination. (destination column's type is a Unicode string DT_WSTR)

I am able to insert Value to SQL Server table but with an incorrect format 㐴〰㌵㠵㔹 expected value is 4400023488.

Any suggestions?

CodePudding user response：

I have two suggestions:

1. Instead of using a data conversion transformation, use a derived column that convert the DT_TEXT value to DT_STR before converting it to unicode:

(DT_WSTR, 4000)(DT_STR, 4000, 1252)[ColumnName]

Make sure that you replace 1252 with the appropriate encoding.

Also, you can use a script component: SSIS : Conversion text stream DT_TEXT to DT_WSTR

2. Use the Hadoop SSIS connection manager and HDFS source instead of using ODBC:

• SSIS Hadoop Connection Manager and related tasks
• Importing and Exporting data using SSIS Hadoop components