August 14, 2012 at 1:49 pm
Hi, Could someone tell me how to resolve this issue.
I changed my oracle source database from 9i to 11g.
Everything worked fine earlier.
But I get the following error now
Code: 0xC02020F6 Source: Load Audit_Log ViewPoint xxxTable_Namexxx Source [1] Description: Column "xxxColumn_Namexxx" cannot convert between unicode and non-unicode string data types. End Error
***Followed by all columns***
I believe it is the 64bit issue.
I am running the job from SQL Server Agent.
August 14, 2012 at 1:51 pm
Old and New database table structure is same.
Client is Oracle 11g.
No derived columns.
But there are CLOB columns.
August 14, 2012 at 2:13 pm
I tried running from command prompt from both 32 bit(Program Files(86) and 64 bit Program Files folders.
Job fails in both
64 bit Error -
Started: 1:02:54 PM
Progress: 2012-08-14 13:02:58.03
Source: Load Audit_Log
Validating: 0% complete
End Progress
Progress: 2012-08-14 13:02:58.05
Source: Load Audit_Log
Validating: 50% complete
End Progress
Error: 2012-08-14 13:02:58.50
Code: 0xC02020F6
Source: Load Table_Name SourceDB Table_Name Source [1]
Description: Column "Column_Name" cannot convert between unicode and non-u
nicode string data types.
End Error
Error: 2012-08-14 13:02:58.52
Code: 0xC02020F6
Source: Load Table_Name SourceDB Table_Name Source [1]
Description: Column "Column Name" cannot convert between unicode and non-unicod
e string data types.
End Error
32 bit Error -
Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Descrip
tion: "ORA-12154: TNS:could not resolve the connect identifier specified".
End Error
Error: 2012-08-14 13:07:03.76
Code: 0xC020801C
Source: Load Table Name SourceTable Name Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAG
ER. The AcquireConnection method call to the connection manager "Source" fai
led with error code 0xC0202009. There may be error messages posted before this
with more information on why the AcquireConnection method call failed.
End Error
August 14, 2012 at 4:50 pm
Unlikely that the move to 64-bit triggers cannot convert between non-unicode to unicode.
Did you change the nls_characterset of your oracle database whilst migrating from 9 to 11?
The oracle client on your server is updated to v11?
August 14, 2012 at 5:06 pm
Hi Jo,
We are not migrating. But the 11g is altogether a new database.
Yes, we have Oracle 11g client installed
Thank you
Chalmsbi
August 14, 2012 at 5:15 pm
I just checked the NLS_CHARACTERSET for old database(9i) is WE8ISO8859P1 and for new datatabse(11g) is AL32UTF8
Is this causing the issue?
August 14, 2012 at 10:00 pm
Almost certain, AL32UTF8 is an unicode characterset. All char/varchar2/... columns can now store unicode characters. You can consider them nchar/nvarchar... by now.
WE8ISO8859P1 is non-unicode.
Because not all characters from AL32UTF8 can be represented in sqlserver varchar columns, the warning/error is thrown.
NLS_CHARACTERSET can't be changed after the oracledatabase is created.
August 15, 2012 at 6:09 pm
Thank You for the reply Jo.
Yes, there is unicode conversion in the source database. What are the options I have now?
We do not have any plans to change the table structure in Target.
We tried changing the NLS_LANG registry of oracle to AL32UTF8
Changed one of the first tables columns to nvarchar.
But the job fails.
Any suggestions please.
August 16, 2012 at 12:42 pm
You should not set the NLS_LANG to AL32UTF8 if the client (SQLSERVER varchar) isn't AL32UTF8 aware.
September 10, 2012 at 1:12 pm
Hi Jo,
The new source character set is in Unicode.
The mappings are done with source DT_STR for source and target external columns.
Do I need to change the code to DT_WSTR for source and Target external columns with unicode conversion transformation?
Or changing the Target SQL Server database character set will resolve the issue?
Thank You.
September 10, 2012 at 2:16 pm
I guess DT_WSTR is the answer. (not sure)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply