Database sources and targets in Jitterbit Design Studio

Databases can be defined as a database source or database target within Jitterbit Studio. This page provides general information about using databases in Jitterbit.

Drivers

Jitterbit communicates with databases through ODBC or JDBC. Cloud agents support a provided set of JDBC drivers. Private agents support both ODBC and JDBC drivers. In addition to provided drivers, you can install additional ODBC and JDBC drivers on private agents.

SQL statements

Manual SQL statements are supported only for JDBC drivers. All SQL statements written using the Formula builder during Transformation mapping should be ANSI-SQL compliant for the JDBC driver of the database you are accessing. There is a 2000-character limit to the SQL statements. A recommended workaround to the character limitation, if needed, is to create database views for long SQL statements.

Authentication

Valid credentials are required to connect to your database. Generally this consists of either DSN (Data Source Name) or username/password.

Unsupported data types

These items are not supported in the current version of the software:

Binary data
Unicode/UTF data
Tables and views with spaces in the names

Supported TLS versions

Transport Layer Security (TLS) 1.2 is supported and recommended for JDBC database endpoints. TLS 1.0 and 1.1 are no longer supported on cloud agents and are disabled by default on private agents version 10.47 and later.

If a database endpoint no longer works after an upgrade to a 10.47 or later agent, add enabledTLSProtocols=TLSv1.2 to the database connection string during configuration of the Database source or Database target to force TLS 1.2 to be used (provided the protocol version is supported by the database endpoint).

On private agents, if TLS 1.2 is not supported by the database endpoint, TLS 1.0 or 1.1 can be re-enabled (though not recommended) by removing TLSv1 or TLSv1.1 from the jdk.tls.disabledAlgorithms security property in the private agent java.security configuration file.

The private agent java.security configuration file is located in these default directories:

Windows: C:\Program Files\Jitterbit Agent\jre\lib\security
Linux: /opt/jitterbit/jre/lib/security

Special characters in database table/column names

If using a private agent, you can specify characters used to define delimiters within database table/column names within the private agent configuration file under the [DbDrivers] section.

If using an ODBC database driver, note that some special characters in database table/column names are unable to be handled by the driver. For example, database fields that have an at sign (@) are not compliant with SQL-based specifications and may not be supported. If the database uses such special characters in table/column names, as a workaround we recommend creating a view on the physical table that does not use the special character in column names and using that instead.

User-defined functions

The User Defined Functions (UDF) tag is found in the Formula builder database functions folder. This function lets you use any database function supported by a database, such as the ToDate() function in Oracle, or the Convert() function in SQL Server.

Update field value

The Update Field Value updates a field from a source database to a given value after an operation is complete. This value is used when performing routine database batch operations to ensure that data that has already been processed during a previous operation is not processed again. It is also useful if the operation is part of a workflow and needs to set the status of a field to the next value indicating that it has completed successfully or a failure occurred.

Primary keys

Primary keys are defined within your database environment. A field that is defined as a primary key is used in indexing the database's information. When applicable, Jitterbit can automatically identify your database's key fields.

Note

Because these key fields are defined within the database environment, you cannot change their setting in the Transformation Wizard.

Database types

Most ODBC- or JDBC-compliant databases are supported. The following are specific recommendations for various database types.

Work with Oracle Databases using TNSNames.ora

The use of JDBC drivers is highly recommended for Oracle databases. However, if you want to use ODBC, it is recommended that you use the Oracle ODBC drivers that come with the version of the database you are accessing. You will need to install the Oracle Client and ODBC driver on the same server that runs Jitterbit.

In using the Oracle Client, you must upload a valid TNSNames.ora file into the appropriate directory which references the database that you wish to access. To reference the database in Jitterbit, enter its TNSNames.ora reference into the Serverfield, together with the appropriate user ID and password.

For more information, see these pages:

Work with Microsoft SQL Server databases

Microsoft SQL Server databases are accessible from Jitterbit agents running on Windows or Linux, although Windows ODBC is recommended for optimized performance. Jitterbit supports the following authentication options:

MS SQL ODBC on Windows using Windows authentication or SQL Server authentication
MS SQL JDBC on Windows using SQL Server authentication
MS SQL JDBC on Linux using SQL Server authentication

For more information see:

Work with file-based databases

File-based databases must make use of the private agent architecture. In order to use file-based databases such as Microsoft Access, the database file must reside on the local file system on the agent machine.

The location and authentication information for flat-file databases is found in either the source or target database definition. To access the database, you do not need to define a Server, only a Database Name.

This table provides examples on accessing file databases with additional connection string parameters if required.

Note

Some databases only require a file path for the Database Name field; no file name is required. In the table below, note that the DBASE and Paradox Database Name entries do not provide any file name. In addition, note that some flat-file databases will require a manual connection string.

Database Type	Driver	Database Name	Additional Connect String Parameters (if required)
Access	Microsoft Access Driver (*.mdb)	\solar\saturn\demo\access\demo.mdb
Access (FileDSN)	Microsoft Access Driver (*.mdb)	\solar\saturn\demo\access\demo.mdb	FileDSN=Access.dsn;
DBASE	MicrosoftdBase Driver (*.dbf)	\solar\saturn\demo\dbase
DBASE (FileDSN)	MicrosoftdBase Driver (*.dbf)	\solar\saturn\demo\dbase	FileDSN=dbase.dsn;
Excel	Microsoft Excel Driver (*.xls)	\solar\saturn\demo\excel\demo.xls
Excel (FileDSN)	Microsoft Excel Driver (*.xls)	\solar\saturn\demo\excel\demo.xls	FileDSN=Excel.dsn;
Paradox	Microsoft Paradox Driver (*.db)	\solar\saturn\Paradox	ParadoxNetPath=\solar\saturn\Paradox;DefaultDir=\solar\saturn\Paradox;
Paradox (FileDSN)	Microsoft Paradox Driver (*.db)	\solar\saturn\Paradox	FileDSN=Paradox.dsn;

For more information, see these pages: