Testing the Integration Server Connection to the Data Source

After verifying the connections between the ODBC, the database client, and the data source, the next step in isolating a connection problem is to connect to the data source databases from Integration Server. Test this connection by running Integration Services Shell on the same computer that runs Integration Server. See Using Integration Services Shell.

Note:

The following procedure uses the sample OLAP Metadata Catalog and sample database. For your tests, substitute the OLAP Metadata Catalog and data source information you are using.

This test uses the LOADMEMBER command to test connections. When the loadmember starts a load, it attempts to make connections in the following order:

Data source, using ODBC
OLAP Metadata Catalog, using ODBC
Essbase Server, using TCP/IP

In this test, you set up these connections one at a time and execute a LOADMEMBER command each time. By examining the errors each time, you can determine which connections are successful.

To test the Integration Server connection to the data source:

Verify that Integration Server is running.
If Integration Server is not running, start it. If Integration Server will not start, check that the Integration Services environment variables are set. See Correcting Integration Server Problems.
On the same computer that is running Integration Server, start Integration Services Shell at a command prompt by typing
```
olapicmd
```
If Integration Services Shell does not start, make sure that the software is installed and that the Integration Services environment variables are set. See Correcting Integration Server Problems.
At the Integration Services Shell command prompt, use the login command to connect to the Integration Server computer; for example, type
```
login cypress
```
Check the release of Integration Services by typing
```
version
```
Make sure that the version of the product you are using is compatible with the ODBC drivers and the version ofEssbase you are using. For information on component compatibility, see Oracle Hyperion Enterprise Performance Management System Installation Start Here.
Set the data source to the sample data source by typing
```
setsource "DSN=TBC;CODEPAGE=English_UnitedStates.Latin1@Binary;
UID=TBC;PWD=password" 
```
The DSN parameter requires the ODBC data source name, which may be different from the database name in the data source.
Set OLAP Metadata Catalog to the sample OLAP Metadata Catalog by typing
```
setcatalog "DSN=TBC_MD;CODEPAGE=English_UnitedStates.Latin1@Binary;
UID=TBC;PWD=password" 
```
The DSN parameter requires the ODBC data source name, which may differ from the OLAP Metadata Catalog database name in the data source.
Set the instance of Essbase Server to which you want to connect by typing
```
settarget "DSN=sequoia;UID=TBC;PWD=password;" 
```
Test the connection to the data source database. Attempt to perform a member load by typing
```
loadmember "APP=OLAP_TBC;DBN=Basic;OTL=TBC Metaoutline;" 
```
Check the status of the preceding command by typing
```
status
```
During a member load, Integration Services Shell connects first to the data source and then to OLAP Metadata Catalog. If the connection to the catalog is unsuccessful, the message “IS Error: Unable to log in to Catalog” is displayed. If the connection to the data source fails, the message “IS Error: Unable to login to Datasource” is displayed.
If the connection to the data source failed, verify the ODBC data source name, user name, and password. If the connection continues to fail, see Correcting Integration Server Problems and Correcting ODBC Problems. Repeat Step 4 through Step 8 of this procedure to retest the connection. When you successfully connect to the data source, continue to the next step.
Test the connection to the OLAP Metadata Catalog database by attempting to perform a member load. Type:
```
loadmember "APP=OLAP_TBC;DBN=Basic;OTL=TBC Metaoutline;" 
```
Check the status of the preceding command by typing
```
status 
```
During a member load, Integration Services Shell connects to OLAP Metadata Catalog and then to the Essbase Server. If the connection to OLAP Metadata Catalog is successful, the message “IS Error: Unable to login to Essbase Server” is displayed. If the connection to OLAP Metadata Catalog fails, the message “IS Error: Unable to login to Catalog” is displayed.
If the connection to OLAP Metadata Catalog failed, verify the ODBC data source name, user name, and password. If the connection continues to fail, see Correcting Integration Server Problems and Correcting ODBC Problems. Repeat Step 9 and Step 10 of this procedure to retest the connection. When you successfully connect to OLAP Metadata Catalog, continue to the next step.
Test the connection from Integration Services Console to the data source by proceeding to Testing the Integration Services Console Connection to the Data Source.