[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 846
  • 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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