Avatar of SQLSearcher
SQLSearcher
 asked on

SSIS Error with OLE DB Command to Oracle

Hello Experts Exchange
I have a SSIS package that has a ADO datasource to MYSQL, it then does a lookup to Oracle, the match data then goes to a OLE DB Command to update the records.

The OLE DB Command is running this script.
Update SITE_INCOME 
Set SI_AMOUNT_BANKED = ?,
    SI_BGC_NO = ?
Where SI_ID = ?

Open in new window


But when I click Refresh in the OLE DB Command I get the following error message;
Error at Data Flow Task [OLE DB Command [99]]: SSIS Error Code DTS_E_OLEDBERROR. AN OLE DB error has occurred. Error code 0x80040E30.

An OLE DB record is available. Source "Microsoft OLE DB Provider for Oracle" Hrsult: 0x80040E30 Description: "Type name is invalid.".

Error at Data Flow Task [OLE DB Command [99]]: Unable to retrieve destination column descriptions from the parameters of the SQL command.

Open in new window


I have googled the error code 0x80040E30 , but have found nothing.

Can anyone suggest something I can try to get this working please?

Regards

SQLSearcher
Microsoft SQL ServerMicrosoft SQL Server 2008SSIS

Avatar of undefined
Last Comment
SQLSearcher

8/22/2022 - Mon
Vitor Montalvão

I can see that you're using "Microsoft OLE DB Provider for Oracle". Don't you have an Oracle Client driver instead?
SQLSearcher

ASKER
Hello Vitor
I have a Oracle ODBC driver but I have not got a OLE DB Oracle driver.

Where would I get that, and how would I install it??

Regards

SQLSearcher
Vitor Montalvão

Should be in your Oracle Client installation media but you can also download it from Oracle's website.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SQLSearcher

ASKER
Hello Vitor
The web site you have given me has Oracle 10g but I have 11g, do you know where I can find the OLE DB drivers for that?

Regards

SQLSearcher
Vitor Montalvão

Check in this webpage. I think there are all the available versions.
SQLSearcher

ASKER
Hello Vitor
Is there not a 32bit version of the 11g Oracle OLEDB driver?

Regards

SQLSearcher
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SQLSearcher

ASKER
Hello Vitor
I have downloaded the file and extract the files on my c drive and run the following command.

install.bat oledb C:\oracle true

but I don't have a Oracle OLE DB driver on my machine.

What should I do next to get it to work?

Regards

SQLSearcher
Vitor Montalvão

Did you follow the install instructions that comes with the zip file?
Should be something similar with this one.
SQLSearcher

ASKER
Thank you for your help.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes