Issue when loading special characters from sybase ASE to SQL server

Question

I have a SSIS Package with Data flow task that has ODBC source and SQL server destination to load data from sybase ASE to SQL server. Its a direct data load without any transformation.

We have a char datatype with a special character in the data where the hex value of that special character is 96 and ascii value is 150 in sybase. In sql server after data load completion when we check the hex value is 1A and the ascii value is 26.
We are seeing the Hex value by using convert(varbinary,columnname) in both sybase and sql server. ASCII is identified using the ascii function in both sybase and sql server as select ascii('special character').

SQL server version: 2017

I tried using OLE DB source and OLE DB destination also and facing same issue. Can anyone help me in resolving this issue.

Answer 1

Hi @Poornima ,

Please add Data Conversion Transformation or Derived Column Transformation between source and destination to change the datatype and code page .

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In data conversion if I try to change the code page to UTF-8 then it is again automatically changes to ANSI 1252.

Cannot convert between unicode and non unicode - getting error message in ole db destination when i try to change the data type to dt_wstr

I tried converting that column in the sybase query in ole db source to nvarchar but no change in result.