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.
All SQL statements written using the Formula Builder during Transformation Mapping should be ANSI-SQL compliant for the ODBC or 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.
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.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:
C:\Program Files\Jitterbit Agent\jre\lib\security
Special Characters in Database Table/Column Names¶
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.
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 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.
Because these key fields are defined within the database environment, you cannot change their setting in the Transformation Wizard.
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:
- Configure an Oracle ODBC Database Source
- Configure an Oracle ODBC Database Target
- Use Proprietary ODBC or JDBC Drivers with Jitterbit
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.
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: