Solved

SSIS - Oracle Client

Posted on 2013-06-16
4
659 Views
Last Modified: 2016-02-11
I'm trying to set up a server with SSIS to connect to Oracle.
The server is windows server 2008 R2; SSIS is VS 2010.

I downloaded the instant client (64 bit) and put it in a directory.  I then set the environmental variables to both the oracle_home and tns_admin (for tnsnames.ora).

Now when I attempt to make an oracle connection in SSIS, I get the following error.

 error.jpg
0
Comment
Question by:patriotpacer
  • 2
  • 2
4 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39251978
What data access provider are you using?

Unless it is the JDBC-Thin connection, I don't think just the Instant Client is enough.

There are additional packages, like ODBC that are available.

I've also seen posts that mention you also need the 32Bit Instant Client but I've never used SSIS or SQL Server so cannot say for sure.
0
 

Author Comment

by:patriotpacer
ID: 39253423
It does have to do with SSIS running 32 bit.

The guidance I'm seeing is to install both the 32 bit and 64 bit.  

I have the 64 bit installed now and all seems fine when I tested an ODBC connection.

But if i also install the 32 bit client what do I set the oracle_home and tns_admin variables to?
0
 

Author Closing Comment

by:patriotpacer
ID: 39253430
Just got it to work!  You were right on the 32 bit thing.

What I did to finish off was to install the 32 bit ODAC install and set the environment variables to the new 32 bit version.

I guess you may need the 64 bit if you want to do an ODBC connection?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39253973
>>I guess you may need the 64 bit if you want to do an ODBC connection?

ODBC doesn't care about 64 or 32 bit.  It's the apps that access the drivers.  64Bit apps, need 64Bit drivers, 32Bit apps need 32Bit drivers.

From what I read about VS and SSIS is that they mix pieces of the apps.  Some of it is 64Bit and some of it is 32Bit.

Google it.  That's how I came across it but most of it I didn't understand since I'm not a MSoft person.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now