'Hadoop to SQL through SSIS Package : Data incorrect format

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?

sql-serverhadoopssisodbcetl


Solution 1:[1]

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hadi

Related Questions