Use a database as a source in Jitterbit Design Studio

Introduction

When you select the option to use a database as a transformation data source, the Jitterbit Design Studio provides a wizard that steps you through the process. The wizard defines one or more Select SQL statements to execute on the server using an ODBC or JDBC driver and fetch the data.

The wizard consists of these steps:

• Search and Select Database Objects
• Specify the number of times each object will be used in a SQL statement
• Define the type of relationship between these objects
• Inserting Additional Where and Order By Clauses

Search and select database objects

This enables you to select the tables and views to fetch data from. You can search multiple times and select multiple database objects (tables, views, and synonyms) in a single transformation.

If you have a large number of objects in your database, search using a string to identify the table that will be the search target. This helps prevent timeouts as it keeps the system from attempting to fetch all objects. Some non-RDBMS ODBC drivers, such as Microsoft Access and Microsoft FoxPro, do not support searches on objects. As these types of databases typically do not store many objects, you can search on all objects without entering a search criterion for them.

Specify the number of times each object will be used in a SQL statement

This is similar to using a table multiple times in the "From" clause of a "Select" statement. Depending on the number of database objects you have selected, the wizard will take you through different steps from here.

If you selected one object:

• You will be prompted to enter the SQL Where clause, if required. Type this using the same syntax as the native SQL that the database expects. You can also include an Order By clause in this section.

• Specify for Jitterbit to update the database once the transformation is complete. Jitterbit is often used for batch jobs that fetch data, transform it to a target, and then update the source database if the operation succeeded, preventing the source data from being processed again. To use this feature:

  • Select an Object Key, which will be used in an update statement to identify the record to update. The key does not need to be a database key.
  • Select an Update Field Value to set fields of that record to a particular value.

An example for the use of this feature is transforming all orders with status "shipping unconfirmed" to a shipping interface target and then updating order status to "shipping confirmed" using Order ID as the key.

If you selected multiple objects, you'll need to create the relationships/SQL joins between objects.

• Define the Parent/Child (one or more) relationships by selecting Parent Tables and Columns and Child Tables and Columns to join.

Define the type of relationship between these objects

This is similar to defining regular joins or outer joins, but allows you to define more constraints if required:

• 1 to N specifies that every parent record must have 1 or more child records.

• 1 specifies that every parent record only has one child record and Jitterbit will only process the first child record it selects.

• 0 to N specifies that every parent record can have 0 or more child records

Inserting additional where and order by clauses

This feature allows for filtering and sorting of data. Using Order By within a child structure will cause it to occur also within the parent structure.

Conclusion

Once you have completed defining your database source, a tree data structure should appear in the source transformation tree to depict what you have created.