We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Error when trying to make SSIS package connecting to oracle

Medium Priority
874 Views
Last Modified: 2012-05-11
Dear experts,

I have ms sql 2008 r2 server installed on a machine along with visual studio 2008

using visual studio 2008, i am trying to create an SSIS package which will take data from an Oracle server (on the same network) and dump it into the local ms sql 2008 r2

however, whenever i try to create a new ole db connection in the ssis package, it gives me this error: (screenie)


please not that i DO NOT have any oracle client or dll or whatever installed on the machine where i am creating the ssis package

please help

1.PNG
Comment
Watch Question

Commented:
Install oracle client and configure tsnames.ora

Here is my example of tsnames.ora:
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARYDB.MYWORK =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.150)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYWORK)
    )
  )

Open in new window


Author

Commented:
oracle client?

im really not familiar with oracle
can you please link me towards the exact installation that i need to set up?

Commented:
What your Oracle server version?

Author

Commented:
10.2

Commented:
Download oracle client from here:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/111060-win32soft-099267.html

Make full install, and configure tsnames.ora, you can find in
[oracle path]\network\admin
My path:
C:\oracle\product\11.1.0\client_1\network\admin

Open in new window


Ask you oracle admin for tsnames parametres.

Author

Commented:
thank you for the link

downloaded the 2nd zip file :: "Oracle Database 11g Release 1 Client (11.1.0.6.0) for Microsoft Windows (32-bit) "

which should i install?? (see screenie)

1.PNG

Commented:
Administrator.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>which should i install?? (see screenie)

I suggest the Instant Client or Custom and select only what you want.  It's the smallest footprint and doesn't provide a lot of utilities you don't need.

Administrator will install a lot of stuff you don't need.  The up side of choosing that is:  You will definitely get what you need.

Commented:
NOT Instant Client !
Install Administrator and don't worry about anything. =)

Author

Commented:
now it gives me the following error : (see screenie)
1.PNG

Author

Commented:
i did configure my ora files, too

Commented:
test connection in Administrative tools ->  Data Sources(ODBC)
 test oracle connection
If you correctly configured your tsnames.ora file, connection will work.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>NOT Instant Client !

Why install about a Gig worth of stuff you don't need?

>>now it gives me the following error : (see screenie)

If you did install everything from a command prompt:

tnsping database_name

where database_name is the entry from your tnsnames.ora file.

Author

Commented:
there's no oracle driver at all when i go to create the system dsn

only sql server drivers are available

Commented:
That mean you didnt install oracle client correctly, try reinstall it.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
This is why I always do a 'Custom' Oracle install.  ODBC might not be part of the default install.

Re-run the installer look at installed products and see if the ODBC drivers are there.  If not, select Custom and select them.

Commented:
2slightww: This why i recomend a 'Administrator' Oracle install. =)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If memory serves, Administrator doesn't install everything.  I think there are still a few optional.

Author

Commented:
i goofed up

i need a 64bit installation thats why it was not working
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
That would do it.  

I have no problem if you want to delete this or have your last post selected as the answer.

Author

Commented:
i cant find the 64 bit client install

can someone please help?

this oracle stuff is fairly new to me
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Why suggest 11gR1?  If you are going to do it why not go with the latest, 11gR2?

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html


I stand by my suggestion of using the Instant Client.  Why install a bunch of stuff you don't need?

http://www.oracle.com/technetwork/topics/winx64soft-089540.html


You need:
 Instant Client Package - Basic
or for English only: Instant Client Package - Basic Lite


Your original question mentioned OLEDB then later you seem to switch to ODBC.  IF you need ODBC, you'll need to addition of:
Instant Client Package - ODBC





Author

Commented:
ok, i downloaded the 64bit client and installed it ... copied the relevant ora files in the appropriate directory

i even checked the connection by making a system dsn with the correct tns & user & password

however, while making the same connections in the ssis package, it gives me an error!
tnserror.PNG
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Why are you using ODBC to test for OLEDB connections?

You are also using the Microsoft OLEDB drivers.  I suggest using the Oracle drivers.

Did you leave the old Oracle install?

Author

Commented:
i removed the old oracle installation before installing the new 64bit version

in SSIS, i dont get any other option in drivers to be able to connect to the ora server
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Are you sure whatever client you installed actually installed the OleDB drivers?

If you did the full client install from a command prompt post the results of: tnsping nbs1112srv

Author

Commented:
here are the results for tnsping :


results.PNG
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Everything from an Oracle standpoint appears to be correct.  There has to be something specific to Visual Studio that isn't looking in the right place.

I cannot help with that.

Commented:
Show your tsnames.ora please.

And select Oracle provider for OLE DB not Microsoft OLE DB proveder for Oracle.

 zz

Author

Commented:
when i use oracle provider for ole db it gives me error (check screenie)


Following is the content of tsnames.ora::

# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.


NBS1112SRV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.52.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = nbs1112)
    )
  )
untitled.PNG
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I asked about the native Oracle drivers in http:#a35512473.

It appears you didn't install them as part of the install.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.