Search code examples
sql-serverhadoopssisodbcetl

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?

Solution

  • 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: