VARCHAR2 should perhaps be DT_WSTR when working with SSIS against a Oracle Database

datatypesoraclessis-2012

When using SSIS with Oracle database, it seems to always assume that a VARCHAR2 maps to a DT_STR. However, if my Oracle encoding is set to AL32UTF8, I should be able to store any unicode character in a VARCHAR2, right? Is there any way to force SSIS to map an Oracle VARCHAR2 to a DT_WSTR? Should there be?

I looked up a few other questions on this, but nothing seems to address it.

I have a data source that has many columns as DT_WSTR, and I want each to go into a VARCHAR2 column without having to convert every column.

Best Answer

Looks like I need to be careful to specify my column length in CHAR. For example, Instead of VARCHAR2(255), I need to ensure it is VARCHAR2(255 CHAR). If I do this, SSIS sees it as DT_WSTR.

Related Solutions

Oracle sort varchar2 column with special characters last

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so:

ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate

You can find a list of supported sort orders here.

Silent truncation of last character when using SSIS with Oracle

It looks like the problem was caused by a data flow task where I had two different data flows running in parallel. The same source tables were involved in both data sources but with different destinations. When I broke the task up into two tasks running one after the other, the truncation problem seems to have gone away.

Best Answer

Related Solutions

Oracle sort varchar2 column with special characters last

Silent truncation of last character when using SSIS with Oracle

Related Question