How can I successfully read unicode with special characters from Oracle db to SQL db through SSIS?
I created a dataflow task which has a source oledb connection to Oracle and a destination oledb to SQL server. While testing the data I noticed that special characters in Oracle are being inserted as ? in SQL server, next I filtered the data of the source query to check only one record and when I did a preview in SSIS it showed with the ? instead of the special character. I tried several things: - Changed the code page to 65001 instead of 1252 but that didn't work. - Changed the source data type from DT_STR to DT_WSTR but still didn't show correctly. - Changed the source driver from "Microsoft OLE DB Provider for Oracle" to "Oracle Provider for OLE DB", however the processing is SO slow it took over 2 hours to process around 3 million records. P.S. The source data type is varchar2 Any suggestions? More info: The column in question is ITEM_DSCR. Source (Oracle): COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION NULLABLE ITEM_DSCR VARCHAR2 120 NULL Y Source OLEDB Connection (SSIS): I changed it to DT_WSTR from advanced editor for OLEDB connection, under OLEDB Source Output (Output Columns). P.S. every time I change the same under OLEDB Source Output (External Columns) it gets changed back automatically to DT_STR. Examples of the special characters involved: ¹ ¬ ¦ ¿
The strange thing is that this is working fine on PROD environment which is using older setup (DTS on SQL Server 2000, calling a stored proc which uses an Open Query to linked server). I also have to note that the destination datatype on PROD is varchar and is storing the special characters perfectly! :D However moving to DEV (SSIS 2012) it is not working properly.
You might need to force the datatype on the way out of Oracle by using `UNISTR(ITEM_DSCR)` in the source SQL, as discussed in this thread: SSIS - Shows DT_WSTR for Non-Unicode Oracle Source. Be sure to refresh the columns after making this change, especially if SSIS keeps changing the column type from `DT_WSTR` to `DT_STR`.