5

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.

|improve this question
  • Try: 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 issues – Philᵀᴹ Apr 2 '12 at 22:54
  • I ran the query and all columns are 12 characters. No truncation. – josjo Apr 3 '12 at 6:56
  • I just want to clarify that the truncation happens in a random pattern. Different rows gets truncated on every export and sometimes there is no truncation at all. – josjo Apr 3 '12 at 6:59
  • Just want to mention I typoed the above query. length() should have been lengthb() (for length in bytes, rather than length in characters). – Philᵀᴹ Apr 3 '12 at 8:22
  • With lengthb() i get exactly double the length for all rows (24 bytes). – josjo Apr 3 '12 at 8:30
4

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.