September 30, 2010 at 7:21 pm
Let me begin by reiterating that this error occurs when run on an SSIS server but not when run on the desktop. I've seen so many posts about people not using the conversion component and this isn't one of those posts.
I created a simple SSIS package that selects a unicode value from an Oracle 11g database and inserts it into a SQL Server 2k8 table. Everything is unicode all the way from the Oracle table to the SQL Server table.
The problem is that while it runs successfully from my desktop, I get the "cannot convert between unicode and non-unicode" error when pushing the package through a job agent task. What's puzzling to me is that I've checked every component of this package all the way through, and it's all unicode. There's got to be an implicit unicode->non-unicode conversion set up in a config somewhere, but I haven't the slightest idea where it is.
I've tried a few things to isolate the error, and see that it's occuring on the OLE DB source component:
Date9/30/2010 5:02:07 PM
LogJob History (job_Oracle_unicode_test)
Step ID1
ServerWHATEVER\WHATEVER
Job Namejob_Oracle_unicode_test
Step Namejob_Oracle_unicode_test
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: AD-ENT\SQLSSAS. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:02:07 PM Error: 2010-09-30 17:02:09.38 Code: 0xC02020F6 Source: unicode my_column OLE DB Source [1] Description: Column "my_column" cannot convert between unicode and non-unicode string data types. End Error Error: 2010-09-30 17:02:09.48 Code: 0xC004706B Source: unicode my_column SSIS.Pipeline Description: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN". End Error Error: 2010-09-30 17:02:09.48 Code: 0xC004700C Source: unicode my_column SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2010-09-30 17:02:09.48 Code: 0xC0024107 Source: unicode my_column Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:02:07 PM Finished: 5:02:09 PM Elapsed: 1.56 seconds. The package execution failed. The step failed.
Other information I can add - we do have a 2k5 server that does not have this problem, but it's the only one I've seen that can run this code successfully. I tried comparing the registry settings between the two servers, looks like NLS_LANG is set properly, client driver versions are identical.
Any suggestions at all that might help figure this out (or maybe an outright solution... shot in the dark, I know,) would be greatly, deeply appreciated. We've tried quite a few things to get variations on this package to succeed, but to no avail.
September 30, 2010 at 11:17 pm
You may find the information in "Solving the Problem" section in this article useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 4, 2010 at 1:19 pm
Thank you for giving this problem a try. It seems you're the only one who wanted to give this a try. However, this is exactly the solution I pointed out at the beginning of my post as being NOT the solution to this particular problem. Adding the data conversion component does not solve this problem, because the source and destination are unicode. There is no conversion needed. The problem appears to be somewhere else.
February 9, 2011 at 9:03 pm
I'm having pretty much the same error...were you ever able to fix this?
May 16, 2011 at 7:17 pm
I am also experiencing this same error. Are there any updates out there for this?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply