A Database target provides write access to databases using JDBC or ODBC drivers. Jitterbit currently supports 32-bit drivers.
Creating a Database Target
You can choose to create a new Database target on its own, or within an existing operation. For more information on how targets work within operations, see Creating an Operation.
Creating a New Database Target as a Standalone Target
Within your project in Jitterbit Design Studio, you create a new Database target by any of:
Go to File > New > New Target; or
- In the tree on the left, right-click on Targets and select New Target; or
In the top toolbar, click the blue target icon.
In the popup, select Database as the type:
Your new target appears in its own New Target tab in the right pane of the window.
NOTE: If you create a standalone target using any of these methods, note that it is not connected to an operation. See Using an Existing Database Target in an Existing Operation below to use the new target.
Creating a New Database Target in an Existing Operation
A target is usually created by default when you create a new operation. (The exception is an operation that consists only of a script.) With an existing operation, you can specify the type of its target by:
Double-clicking on the target icon; in the popup, select Create New Target; or
- Right-clicking on the target icon and selecting Create New Target.
In the configuration screen that appears, use the Type dropdown to select Database, as shown above.
Using an Existing Database Target in an Existing Operation
To use an existing Database target in an existing operation with a target, you can set it by any of:
Within the operation, double-clicking on the target icon, and in the resulting popup, selecting the desired Database target from the list; or
Within the operation, right-clicking on the target icon, choosing Select Existing Target, and in the resulting popup, selecting the desired Database target from the list; or
- Dragging the desired Database target from the tree on the left and dropping it onto the existing target icon of the operation.
Configuring a Database Target
After you have created a Database target, the configuration screen will open in the main view of Design Studio. You can return to the configuration screen at any time by double-clicking on the target icon in the operation, or by double-clicking on the target in the tree on the left.
The configuration screen will appear similar to this example:
- Name: Enter an appropriate, unique name for the source.
- Type: Use the dropdown to select Database, if it is not already specified.
- Connection Parameters: Specify the details for your database connection:
- Driver: Enter a driver name, either manually or by clicking the select Button (preferred) next to the driver field. Depending upon the driver selected (see Database Drivers below), certain fields in the source configuration are enabled or disabled.
- Server Name: Enter either the server name or IP address of the server to be accessed.
- Database Name: Enter the database name to be accessed.
- Login: If a login is required, enter a valid username into the Login field.
- Password: Enter a password to use with the Login to access the database.
- Use default port: The default port will vary depending on the database driver selected. For a different port, deselect the Use default port checkbox and enter a port to use in the Port field.
- Test Connection: Clicking the button will test the Database source; it will fail if it is unable to make a connection to the database server with the provided connection parameters.
Click the Save button in either the main toolbar or the target's toolbar to save the configuration.
The database drivers dialog will appear similar to this example:
Clicking the Refresh button will update the displayed list of drivers available on the Agent. The list can be filtered by type (JDBC or ODBC) and name. (ODBC drivers are supported only on Private Agents.) Depending upon the driver selected, certain fields in the source configuration are enabled or disabled. To add additional drivers to an Agent, see Installing Additional ODBC or JDBC Drivers.
Additional connection parameters can be specified by clicking the Options at the bottom of the Connection Parameters section:
- Optimize for Bulk Data: Available when using a Microsoft SQL Server or an Oracle database as a transformation target, enabling this option improves performance when upserting large amounts of data. (Available as of Harmony version 8.7.0.)
Transaction Isolation Level: Specify an isolation level using the dropdown menu. The DEFAULT setting uses the driver or database server default transaction isolation level. Driver-specific levels are not supported; available are Transact-SQL transition isolation levels:
Timeout (sec): Specifies the limit to wait for a successful database connection in seconds. Default is 300 seconds (5 minutes).
Additional Connection String Parameters: Additional parameters that should be added to the database connection string can be entered in this field. Connection string parameters are specific to the database driver you use (i.e. Microsoft SQL, MySQL, PostgreSQL, Oracle, etc.). Review the driver specifications to determine the required parameters.
- Construct connection string manually: Checking this allows entry of the connection string manually in the field below the checkbox. It will then disable the Additional Connection String Parameters. See Manual connection string for details.
TIP: See Converting a Database Source or Target to Use a JDBC Driver for instructions to convert from an ODBC driver to a JDBC driver.