Oracle 11g and SSIS error: cannot convert between unicode and non-unicode string data types
When upgrading our Oracle database to 11g we received the following error when executing previously working SSIS packages on our MSSQL2008R2 server:
Executed as user: <redacted> Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 3:10:34 PM Error: 2013-08-16 15:10:37.14 Code: 0xC02020F6 Source: <redacted> OLE DB Source  Description: Column “<redacted>” cannot convert between unicode and non-unicode string data types. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC004706B Source: <redacted> SSIS.Pipeline Description: “component “OLE DB Source” (1)” failed validation and returned validation status “VS_ISBROKEN”. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC004700C Source: <redacted> SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2013-08-16 15:10:37.15 Code: 0xC0024107 Source: <redacted> Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:10:34 PM Finished: 3:10:37 PM Elapsed: 2.308 seconds. The package execution failed. The step failed.
Turns out the fix was very simple. Basically all we had to do was edit the package in notepad and replace all occurrences of: