If your ODBC connections require database client software, the first step toward isolating connection problems in Integration Services is to attempt to connect the database client software to the data source that you are using. After you have verified that these components are communicating properly, you have a good base from which to test the remainder of the connection chain.
The most likely cause of a connection failure at this point is that the database client software environment variables are not included in the login script for the user who runs the Integration Server program.
To test the database client connection to the data source:
Log on to the computer running Integration Server as the user who starts the Integration Server program (typically, hyperion).
Start a database client utility program with which you can use SQL statements to connect to and query databases, such as a command-line SQL utility.
If the database client utility does not start, check to make sure that the client software is installed and the required environment variables are set. See Correcting Database Client Problems.
Connect to the OLAP Metadata Catalog database in the data source using a valid database user account.
If the connection fails, try to connect as a user who you know has access permission, such as an administrative user. If the administrative connection succeeds, check the other user accounts and permissions in the data source for the OLAP Metadata Catalog database, and carefully check user accounts with connection problems. See, Correcting Data Source Problems.
Execute an SQL select statement against a table in the OLAP Metadata Catalog database; for example, if you are connected to the sample OLAP Metadata Catalog, type
SELECT * FROM TBC.OV_INFO
If the select statement fails, check the permissions for the user name you are using to connect to the data source. See Correcting Data Source Problems. Also, check that the version of the database client software you are using is compatible with the version of the data source. See Correcting Database Client Problems.
After you have successfully completed the preceding steps, try connecting to OLAP Metadata Catalog from Integration Services Console.
See Testing the Integration Services Console Connection to the Data Source. If the connection fails, proceed to Testing the ODBC Connection to the Data Source.
If you still cannot complete a connection and select statement with the user who runs Integration Server, contact technical support for the data source.
Repeat Step 3 through Step 5 for the data source database.
After you have successfully connected to OLAP Metadata Catalog, you can rule out setup and environment problems on the Integration Server computer and concentrate on problems specific to the data source database, including required database server names, database names, user names, passwords, and access permissions.