SSIS, BIDS, and TNSNAMES

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.

Advertisements