I have an ETL that is importing tables from Oracle to SQL 2008 using the OLEDB FastLoad. The data in Oracle is non-unicode. When the table is created in SQL it is created with unicode datatypes. For some reason the datatypes are being forced from non-unicode to unicode. Do any of you know of a way to stop this from happening? Possibly a Oracle driver problem?
I'm presuming you are using SSIS?
Guess what, SSIS wants everything to be unicode, so it assumes that all incoming data is in unicode. If you don't want it to be unicode, you will need to convert each field using a dataconversion task.
This is something you might want to try. Check the value of the NLS_LANG variable in the Oracle Database you are importing to. Changing this variable before running the ETL could help you.
Check the NLS_LANG faq here: http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm