• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 865
  • Last Modified:

Error when trying to make SSIS package connecting to oracle

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
0
RakeshBhandari
Asked:
RakeshBhandari
  • 13
  • 11
  • 10
1 Solution
 
yadayaCommented:
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


0
 
RakeshBhandariAuthor Commented:
oracle client?

im really not familiar with oracle
can you please link me towards the exact installation that i need to set up?
0
 
yadayaCommented:
What your Oracle server version?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RakeshBhandariAuthor Commented:
10.2
0
 
yadayaCommented:
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.
0
 
RakeshBhandariAuthor 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
0
 
yadayaCommented:
Administrator.
0
 
slightwv (䄆 Netminder) 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.
0
 
yadayaCommented:
NOT Instant Client !
Install Administrator and don't worry about anything. =)
0
 
RakeshBhandariAuthor Commented:
now it gives me the following error : (see screenie)
1.PNG
0
 
RakeshBhandariAuthor Commented:
i did configure my ora files, too
0
 
yadayaCommented:
test connection in Administrative tools ->  Data Sources(ODBC)
 test oracle connection
If you correctly configured your tsnames.ora file, connection will work.
0
 
slightwv (䄆 Netminder) 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.
0
 
RakeshBhandariAuthor Commented:
there's no oracle driver at all when i go to create the system dsn

only sql server drivers are available

0
 
yadayaCommented:
That mean you didnt install oracle client correctly, try reinstall it.
0
 
slightwv (䄆 Netminder) 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.
0
 
yadayaCommented:
2slightww: This why i recomend a 'Administrator' Oracle install. =)
0
 
slightwv (䄆 Netminder) Commented:
If memory serves, Administrator doesn't install everything.  I think there are still a few optional.
0
 
RakeshBhandariAuthor Commented:
i goofed up

i need a 64bit installation thats why it was not working
0
 
slightwv (䄆 Netminder) Commented:
That would do it.  

I have no problem if you want to delete this or have your last post selected as the answer.
0
 
RakeshBhandariAuthor Commented:
i cant find the 64 bit client install

can someone please help?

this oracle stuff is fairly new to me
0
 
slightwv (䄆 Netminder) 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





0
 
RakeshBhandariAuthor 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
0
 
slightwv (䄆 Netminder) 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?
0
 
RakeshBhandariAuthor 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
0
 
slightwv (䄆 Netminder) 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
0
 
RakeshBhandariAuthor Commented:
here are the results for tnsping :


results.PNG
0
 
slightwv (䄆 Netminder) 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.
0
 
yadayaCommented:
Show your tsnames.ora please.

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

 zz
0
 
RakeshBhandariAuthor 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
0
 
slightwv (䄆 Netminder) Commented:
I asked about the native Oracle drivers in http:#a35512473.

It appears you didn't install them as part of the install.
0
 
yadayaCommented:
Well.

Plan A:
- Right click on the C:\Oracle folder (the root folder where your client is installed) and choose Properties.
- On the security tab add the local machine's Users group. Grant Read & Execute, List Folder Contents and Read privileges.
- Click "Apply".
- Click "Advanced"
- Check "Replace permission entries on all child objects ..."
- Click "Apply". This will take a while to propagate the security grants down to every file and folder of the Oracle client.


Plan B:
Execute if you installed Oracle 10 client
regsvr32 %ORACLE_HOME%\BIN\OraOLEDB10.dll

Open in new window


Plan C:
Totaly remove oracle from your computer and install again.
0
 
RakeshBhandariAuthor Commented:
thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 13
  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now