1

My package runs fine from both my desktop and my ETL server when I RDP into it. However, when running as part a job, I get the following error on all my string columns: "Error: Column "***STATUS" cannot convert between unicode and non-Unicode string data types."

The error occurs on an OLE DB Command component that updates a table in an Oracle database. None of my columns on either the SQL/SSIS side nor the Oracle side are Unicode. Here's the metadata directly leading into my OLD DB Command component.

metadata

I verified that the External Columns on the OLE DB Command component in question exactly match that metadata. I've also tried explicitly converting the columns to Unicode before inserting in case they were Unicode (I know they're not) on the Oracle side, but that leads to a hard error (red X) and the same message.

Here's the Oracle schema: enter image description here

Command:

enter image description here

Anyone have any idea on how to get this to run from the agent?

Hadi
  • 36,233
  • 13
  • 65
  • 124
James G
  • 118
  • 11
  • Can you provide a screenshot of the data flow task? also the OLEDB command and the destination schema if exists? – Hadi Mar 19 '19 at 19:43
  • The data flow task is just a SQL source going directly into the OLE DB Command. The destination schema is pictured in the OP. The Command is a simple update, but I'll add it. – James G Mar 19 '19 at 22:37
  • Based on that, I think it is a 32/64 bit issue. check the answer below – Hadi Mar 19 '19 at 22:40
  • What is the oracle provider installed (32 or 64 bit) and what is the (Operating system platform)? – Hadi Mar 19 '19 at 22:48
  • I agree, I think it's this. Just posted a more detailed response in the other thread. – James G Mar 19 '19 at 23:00

2 Answers2

0

Based on the following oracle support case:

The following error message cause is:

Developing an SSIS package that uses the Oracle OLEDB Provider on a 32 bit operating system and then deploying to a 64 bit SQL Server installation

Possible Workarounds

Note that i didn't tested these workarounds before.

(1) Try running the package in 32-bit mode:

From Visual Studio

    GoTo Project properties >> Debugging >> Run64BitRuntime  = False

enter image description here

From SQL Agent

Check the following link:

(2) Install Oracle x64 oledb provider

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Though I'd forgotten all about it, I've had #1 work before. This wan't one of those times, however. We've had ODAC 32 & 64 installed on this server for years with dozens of jobs using them, but you still may be on to something. The job stopped working after our Oracle team did a database migration to 12.2. It's possible we still have ODAC 11G on our ETL server (though it means someone installed 32, but not 64 bit 12 since it runs live). I will be exploring this and will post when I have an answer. – James G Mar 19 '19 at 22:57
  • @JimG i will be waiting for your reply and always ready to help. Good Luck – Hadi Mar 19 '19 at 23:06
  • I installed ODAC 12 (32 and 64 bit) drivers on the ETL server and I still get the same error. I also tried running the package in 32 bit mode to no avail. The package selects then updates 4 records. I hate to say it, but it might just be TSQL cursor time. – James G Mar 26 '19 at 20:04
  • @JimG if you take a look at the reason mentioned by oracle: `Developing an SSIS package that uses the Oracle OLEDB Provider on a 32 bit operating system and then deploying to a 64 bit SQL Server installation` i looks like that the problem is caused by a platform mismatch between development OS and Deployment SQL Server. I mentioned the workarounds above as **Possible** workaround and it looks like they didn't work. In the Oracle link above they mentioned a solution but you have to be an Oracle customer to be able to read it. And i didn't find any other link mentioning that solution. Good Luck – Hadi Mar 26 '19 at 20:12
  • @JimG try opening a bounty on this question so it may get more attention by other users. – Hadi Mar 26 '19 at 20:13
0

On the OLE DB Source ensure you have delay validation set to true.

I set the default code page to false and had the code page as 1252.

I have tried this both with 32 bit and 64 bit.

Also need to make the flag validateexternalmetadata = false

Amit Verma
  • 8,660
  • 8
  • 35
  • 40
Paul
  • 1
  • 2