I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. I have tried using different SSIS data types (DT_STR, DT_WSTR and DT_NTEXT) but the problem persists. The source string is always 12 characters long ([0-9A-Z] only), but when I check the destination database some columns are missing the last character.
I use the latest 32bit Oracle Instant Client (11.2) with ODBC and SQL Server 2008 R2. I use ADO .NET for both source and destination. The source character set is AMERICAN_AMERICA.WE8ISO8859P1 and the source and destination columns are declared as VARCHAR2 (12 BYTES) and NVARCHAR(12) respectively.
select length(to_nchar(source_column)), count(*) from source_table group by length(to_nchar(source_column));
and see if you'll get any truncation due to VARCHAR->NVARCHAR issueslength()
should have beenlengthb()
(for length in bytes, rather than length in characters).