Today’s alphabet soup has to deal with an issue we struggled over this week at work. For those unfamiliar, SSIS is SQL Server 2005 Integration Services. It’s the rewrite of SQL Server 2000 DTS (Data Transformation Services). You use SSIS to pull data from one database and put it into another.
You develop SSIS in BIDS, Business Intelligence Developers Studio. BIDS is Visual Studio with the BI tools added in. The issue we’ve been fighting had to do with getting our SSIS packages to use Oracle 10g drivers to talk to the Oracle 9i data source that was the beginning point for our packages.
Over and over we got an error that the OLEDB provider could not “Acquire” the database, yet the data source kept passing the “Test Connection” test. It was one of my associates who came upon the clue.
In our environment, we don’t normally use a TNSNAMES.ORA file. TNSNAMES is where Oracle normally stores information about a schema name. Schema names are kind of like URLs. You use a human readable schema name, something like WAREHOUSE1. Oracle driver then goes into TNSNAMES to find the server information so it knows where to route the data to.
As I said, in our environment we don’t use TNSNAMES, instead we have another file. The data driver looks there to get the server that TNS info is stored at for the whole company. This allows us to have a single place to store (and update) TNS data instead of thousands of workstations.
The problem was, BIDS didn’t like that. Even though we had it redirected, BIDS didn’t recognize that and kept going to the TNSNAMES file to find schema info, and in our case it couldn’t find it because it wasn’t there. Once we added a TNSNAMES file BIDS was able to acquire it’s schema info, connect to the database and get whatever data it needed.
The moral of the story is, if you are having problems in BIDS connecting to an Oracle data source verify that you do indeed have a valid TNSNAMES.ORA file.
Arcane Code 
How do i verify that i do indeed have a valid TNSNAMES.ORA file.
Further, where does BIDS look for TNSNAMES.ora? I am setting up a conn to Oracle on a 64 bit Windows machine. tnsping sees the data source in question, but thru SSIS it gripes about it doesn’t know how to resolve the connect identifier specified.
Also am having a time pulling the info up thru my friend Google, for whatever reason. So if you could please let us know, infinite gratitude is yours!
On the workstation where you are running BIDS, look in your Oracle Home directory, then in the network\ADMIN folder for your TNSNAMES.ORA. Your Oracle Home will be whatever was set during your install of the drivers, so it could be anywhere.
Worst case do a search, or open up a CMD window and do a “dir tnsnames.ora /s” (omit the quotes) from the C:\ prompt to let the machine find it for you. Ignore any that pop up in a SAMPLES folder.
If you don’t have one, copy the SAMPLES one into the \network\ADMIN folder and edit it for your environment.