Deploy SSIS package to production which has a connection to oracle databse

Hi There,

In SSIS package development environment, I was able to connect to an oracle database and pull data into my sql server database. I installed the client tools for oracle and I put an entry into the tnsnames.ora and I was able to connect.

But in production environment, if I deploy the package on sql server, I was wondering if I had to do the same job of downloading the oracle client tools onto my production machine --which creates a tnsnames.ora file to it default location and then edit it with tthe tns entry-- or is there a better way to do this--avoiding the download?

Can somebody plzz help me ?

Thanks.

You need to install the Oracle Client (9i) or (10) on the actual SQL Server box.

This is for the network libraries. There is a special build of the client for Windows 2003 Server if you go to Oracles site and have a look around

cheers Dave

|||

You do need to repeat the same steps like you've done in your development environment: install Oracle client and adjust tnsnames.ora file.

There are 2 more options:

1) There is another "lighter" option - Oracle Instant Client:

http://www.oracle.com/technology/tech/oci/instantclient//index.html

2) The Oracle Data Provider for .NET (ODP.NET) . It has Oracle client bundled with it.

http://www.oracle.com/technology/tech/windows/odpnet/index.html

Regards,
Yitzhak

|||

Hi Yitzhak,

If I intstall the lighter option, where do I adjust the tnsnames.ora file? I don't find the file after installation. Do I need to manually create it and put it in a specific location? Can you please reply back?

Thanks.

Sam.

|||

Also, which of the instant client packages should I download? There are 5 downloads for install client for 64bit.

Thanks.

Sam.

|||

Oracle has Instant Client FAQ page:

http://www.oracle.com/technology/tech/oci/instantclient/ic-faq.html#A5028

How do I ensure that my "tnsnames.ora" file is being used in Instant Client?
Always set the TNS_ADMIN environment variable or registry setting to the full path of the tnsnames.ora file. This practice will ensure that you are using the appropriate tnsnames.ora for your application when running with Instant Client.

Regards,
Yitzhak

|||

Because you are using MS SQL Server 2005 SSIS on Windows OS, just 2 options are applicable for you:

Windows Itanium 64-bit 10.2.0.2|||

HiYitzhak,

Can you please bare with me? I downloaded the windows x86 64 bit install client and this is what I did.

I unzipped the folder contents to c:install\instantclient

I created a tnsnames.ora file with the tns entry under 'C:\'.

I added the full path 'c:install\instantclient\' to the path environment variable

I also added a tns_admin environment variable giving it a value 'C:\tnsnames.ora'.

Now I test executing the package using dtexec and I still can't connect to the oracle database. It still gives me an error 'Client and networking components not installed. Am I missing smth?

Can you please help me?

Thanks.

|||

There is a command line tnsping.exe Oracle utility to check both client installation and tnsnames.ora settings.

You can try to launch it at the command prompt:

c:\>tnsping oracle-instance-name

Regards,
Yitzhak