Connections to Database Servers

The Connection Store lets you create Connections to Oracle and SQL Servers. This connection information is saved in one place and can be referenced in one or more Jobs.

Adding a Connection to an Oracle Database Server

This option is available only if the Oracle Integration has been installed. See Configuring the OracleStoredProc Execution Method for more information.

Ensure you have created a Credential within JAMS that contains the credentials for connecting to the Oracle database.

  1. Click Connection Store from the Shortcuts menu.
  2. Click +.
  3. In the Name field, enter the name, such as OraDatabase.
  4. In the Description field, enter a description.
  5. In the Type of Connection field, select Oracle.
  6. Click Ok.
    NOTE: You can also change the Type of Connection on the Properties tab. If you change it, the Property values are maintained if they exist in the new Connection Type.
  7. Prepare your Oracle Connection String to the Oracle Database. Within the following string, replace the following:

    HOST with the Oracle database host name.

    SID with the Oracle database service name.

    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <HOST>)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = <SID>)));

    NOTE: After updating the HOST and SID, copy the Connection String because you need this to update the Connection String field. Use tnsnames.ora as the reference to look up the required values for the Connection String.
  8. Click the Properties tab.
  9. Under Oracle, click the Connection String field. The Oracle Connection dialog is displayed.
  10. In the Data Source field, paste the Connection String.
    NOTE: Do not include Data Source = in the Connection String as JAMS automatically inserts it.

  11. In the Credential field, select a Credential in JAMS.
  12. Click Test Connection to verify the connection. If the connection is unsuccessful, a red X is displayed on the Test Connection button. You can hover your cursor over it to get details about the error.
  13. Click OK.

  14. Click the Security tab.
  15. On the Security tab, review the permissions for this Connection.
    NOTE: Ensure the Submit permission is selected for user accounts that will use this Connection.
  16. Click Save and Close.

Oracle Connection

The example below shows a JAMS Job that uses the OracleStoredProc Execution Method. You can define the Oracle Connection Store Definition on the Source tab of the Job Definition.

Adding a Connection to a SQL Server

Ensure you have created a Credential within JAMS that contains the credentials for connecting to the SQL Server database. The Credential can use a domain service account for use with Integration security or a SQL Server user account (username/password) for use with SQL Authentication. This can be overridden on the Job level. The Credential can also be used for the Impersonation User.

  1. Click Connection Store from the Shortcuts menu.
  2. Click +.
  3. In the Name field, enter the name, such as SQLDatabase.
  4. In the Description field, enter a description.
  5. In the Type of Connection field, select SQL Server.
  6. Click Ok.
    NOTE: You can also change the Type of Connection on the Properties tab. If you change it, the Property values are maintained if they exist in the new Connection Type.
  7. Click the Properties tab.
  8. Click the Connection String field.
  9. Select an Impersonation User to be used for testing the Connection String within the Connection String Builder, if using Integrated Security. (You can also use SQL Authentication or Azure Authentication.)

  10. In the Server Name field, enter the SQLServer\Instance.
  11. In the Database Name field, select the Databases on the SQL Instance.
  12. Click Test Connection to verify the connection.
  13. Click OK.
  14. In the Credential field, select a Credential in JAMS.
  15. Click the Security tab.
  16. Review the permissions for this Connection.
    NOTE: Ensure the Submit permission is selected for user accounts that will use this Connection.
  17. Click Save and Close.

SQL Connection

The example below shows a JAMS Job that uses the SSISDirect Execution Method within JAMS. You can define the SQL Connection in the Stored Connection field under the Source tab of the Job Definition.