May 24, 2016 at 6:43 am
My issue is two fold and relates to simply creating a connection to databases (please understand this might be very basic, but as this is my first SSIS attempt, I am very confused on the basics).
We are doing our development on our servers with SQL Server and IS Installed, not from our local computer. When we access our servers, we need to use TPAM for all server access. As such, how do I configure a SQL Connection inside my SSIS project to connect to SQL Server using a predefined Windows Service Account (we cannot use SQL Logins for any database access, as all access must be granted via a domain service account).
I need this package to connect to Oracle and pull data into our SQL Server database. When I try and create an Oracle connection using "OracleClient Data Provider", I get an error stating "Attempt to load Oracle client libraries threw BadImageFormatException. The problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed". Based on this error, I installed varous options from "winx64_12102_client", but still getting this error. What needs installed to connect to Oracle?
If I try to connect to Oracle using "Microsoft OLE DB Provider for Oracle", I get the following error "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation". I did install various options from the above mentioned Oracle install, but this didn't seem to help.
Could anyone point me in a direction to get this working? Google did not help me with teh SQL Server connection, and I found a bunch of "stuff" based on the Oracle errors, but many items stating different "packages" that need installed and I do not want to start installing anything and everything related to Oracle on our servers, but only what is needed which doesn't seem to be what I ran.
May 24, 2016 at 2:40 pm
To use a domain account to connect to servers, you'll need to set your connections up with the option 'use windows authentication'. You will then need to run the package from the AD service account that has access to the server. For development, you can launch SQL Server Data Tools by holding down shift right-clicking on it from your start menu, and selecting "Run as different user...". You will then be prompted for credentials.
Do you have the Oracle client software installed?
If you do, it sounds like you have the 32-bit client installed. In that case, you'll want to go to your project properties, and under Configuration Properties/Debugging you'll want to change Run64BitRuntime from True to False.
May 25, 2016 at 5:37 am
Thank you for the help. One issue was solved (connecting to SQL Server) but I still get that Oracle error when changing the property. I also installed Oracle Client, Runtime and Administrator from the file winx64_12102_client found on the Oracle Website. This didn't help the Oracle connection issue.
May 25, 2016 at 8:02 am
Ugh, well this is where things get a little weird.
In theory, you should be able to write a 64-bit package that uses the 64-bit client and everything works well.
Unfortunately, the development environment is still a 32-bit application, so if you try to run your package from there it will fail since there is no 32-bit oracle provider available.
To work around this, you will need to install *both* the 32-bit and 64-bit Oracle client software.
Normally I recommend installing the 32-bit first, then the 64-bit. This is because you can only have one set of Oracle OLE providers registered at a time, and installing the 64-bit second will make sure that the 64-bit OLE provider is the one that is registered.
In your case, you've already installed the 64-bit client. When you install the 32-bit client, the 32-bit OLE providers will be registered.
Once you've installed the 32-bit client, you can work around this in two ways:
1.
Change the configuration of your project to run as 32-bit. If you plan on running the SSIS package from a job in SQL Server, you will need to go to the execution options of the job step and check the checkbox that says "use 32-bit runtime".
This should work fine for your packages, but if you also plan on creating any linked servers from SQL Server to Oracle, you will need the 64-bit provider registered instead. In that case....
2.
You can manually re-register the 64-bit OLE providers.
To do this, you will need to open a command prompt, and navigate to the bin directory of the 64-bit Oracle client.
From there, you can manually register the 64-bit OLE provider using the following command:
regsvr32.exe OraOLEDBxx.dll
Note that the name of the dll file will have a number instead of xx. I believe the number depends on what version of the client you have installed.
This will allow you to run your SSIS package from a job as a 64-bit package, and will allow you to create linked servers to Oracle.
HOWEVER - It WILL break your ability to run the package from the development environment!
If you need to run your package as 64-bit on the server or need to create linked servers, I recommend doing your package development on a separate machine with the 32-bit Oracle client installed and leaving just the 64-bit Oracle client installed on the server.
I hope this helps.
May 25, 2016 at 8:20 am
Oh, I should also probably mention that your Oracle client needs to be configured for the Oracle server you are trying to connect to.
https://docs.oracle.com/cd/B16276_01/doc/server.102/b14196/network005.htm
The SID that you provide in the configuration is what you will reference when you set up your Oracle connection in SSIS.
May 25, 2016 at 9:04 am
Thank you for all the help. I really appreciate.
But unfortunately, I cannot install the 32-bit drivers. I am trying to install from the follow: "winnt_12102_client32" that I downloaded from the Oracle website. I go to install "instantclient" option and then to install goes through the "Perform Prerequisite Checks" and all is good there, so I click the "Install" button. About two seconds later, the installer simply closes and nothing is installed.
May 25, 2016 at 10:08 am
I am not very familiar with the instant client. I know that it does not include the TNSNAMES.ORA file used to configure the SID used to connect to your database. I always install the full client.
May 25, 2016 at 10:33 am
No worries. You have already been most helpful and helped me get past a few issues. Very much appreciated.
May 25, 2016 at 10:56 am
Just an FYI (in case anyone comes across this thread later for help), here is an article I had to follow to get the 32-bit drivers installed:
https://community.oracle.com/thread/3725625?start=0&tstart=0
I had to create the Registry setting
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply