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
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I can see that you're using "Microsoft OLE DB Provider for Oracle". Don't you have an Oracle Client driver instead?
SQLSearcherAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
Should be in your Oracle Client installation media but you can also download it from Oracle's website.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

SQLSearcherAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
Check in this webpage. I think there are all the available versions.
SQLSearcherAuthor Commented:
Hello Vitor
Is there not a 32bit version of the 11g Oracle OLEDB driver?

Regards

SQLSearcher
Vitor MontalvãoMSSQL Senior EngineerCommented:
You never mentioned that you needs a 32bit version.
Lucky that they have a similar webpage for 32bit :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
Did you follow the install instructions that comes with the zip file?
Should be something similar with this one.
SQLSearcherAuthor Commented:
Thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.