Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|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

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 and thanks for coming back with your solution –  Jack Douglas Apr 3 '12 at 8:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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