Database to database wizard in Jitterbit Design Studio

Jitterbit Connect^TM provides a fast, easy route to data transformation. This is made possible through a wizard-based integration tool built directly into Jitterbit. The wizard creates for the user all of the complex, detailed operations and components of a Transformation project. By stepping through the wizard, users can create an integration project without the need for coding.

Use the following steps to create a Database to Database wizard.

• Open Jitterbit and click the Jitterbit Connect icon at the top of the screen.

• The Jitterbit Connect Wizard menu appears.

• To start the wizard, click the Database to Database button in the Standard column in the Jitterbit Connect Wizard menu, which displays the Database Source Selection screen.

• Begin creating your project with the wizard.

Create source database structure

• To use an existing Source database, select from the drop-down and click Edit.

• To create a new Source database, select the Create New button from the drop-down.

• If you wish to write your own SQL statement for the Source, check the "Write SQL statement instead of using the Wizard" checkbox.

Complete the fields as follows:

Complete the connection parameters fields as follows:

Test Connection:

• Click the Test Connection button to test your connection to the endpoint. A dialog box appears confirming that the connection was successful. If the connection test is unsuccessful, go back and check your parameters. You may also contact Jitterbit support.

After you have completed your fields:

• Click Next to display the Source Table Selection screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Create table selection

• Click the Download List of Tables button to download the list of available tables.

• Optional:

  • You may enter a search string as a filter. Check the Table or View checkboxes to limit the objects to select.

  • You may select the checkbox for including the schema/owner.

• Once the tables are downloaded, you can copy tables from the Available Tables column to the Selected Tables column.

• Highlight the tables to be copied in the Available Tables column, and click the right arrow. The selections are then copied to the Selected Tables column.

After completing your tables:

• Click Next to display the Source Hierarchical Relationships screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Create hierarchical relationships

• Click the Select Parent Table drop-down and make a selection. In this example, we have selected OrderHeader from the list.

• Click the Select Child Table drop-down and make a selection. In this example, we have selected OrderDetail from the list.

• Then click the Join Tables button. The Join Properties dialogue appears.

• In the Link Keys section, drag and drop an item in the parent column (OrderHeader in this example) to the child column (OrderDetail in this example) to link them. As an alternative, you may select an item in the child column on the right to activate the Link button, then click the Link button to link the columns as shown below.

• You may select an item in the child column on the right to activate the Remove Link button, then click the Remove Link button to remove the link.

• Click the Column Display Order button to select Database (the default) or Alphabetical.

• After completing the parent and child joining, select a radio button to identify the join type. The default is 1 to N. In this example, we have left the default at 1 to N.

• Click OK to return to the Source Hierarchical Relationships screen, which displays your table tree showing the currently defined relationships.

• Click Cancel to return to the Source Hierarchical Relationships screen without saving your entries.

• Click Back to return to the previous screen.

After completing your tables:

• Click Next to display the SQL Where Clause screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

• On this screen, you may optionally enter a SQL Where Clause.

• Click Next to display the Target Database Selection screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Create the Target

• To edit an existing Target database, select from the drop-down and click Edit.

• To create a new Target database, select the Create New button from the drop-down.

Complete the fields as follows:

Complete the connection parameters fields as follows:

After you have completed your fields:

• Click Next to display the Target Table Selection screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

• Click the Download List of Tables button to download the list of available tables.

• Optional:

  • You may enter a search string as a filter. Check the Table or View checkboxes to limit the objects to select.

  • You may select the checkbox for including the schema/owner.

• Once the tables are downloaded, you can copy tables from the Available Tables column to the Selected Tables column.

• Highlight the tables to be copied in the Available Tables column, and click the right arrow.

• The selections are then copied to the Selected Tables column.

After completing your tables:

• Click Next to display the Target Hierarchical Relationships screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Create hierarchical relationships

• Click the Select Parent Table drop-down and make a selection. In this example, we have selected OrderHeader from the list.

• Click the Select Child Table drop-down and make a selection. In this example, we have selected OrderDetail from the list.

• Click the Join Tables button. The Join Properties dialogue appears.

• In the Link Keys section, drag and drop an item in the parent column (OrderHeader in this example) to a child column (OrderDetail in this example) to link them. As an alternative, you may select an item in the child column on the right to activate the Link button, then click the Link button to link the columns as shown below.

• You may select an item in the child column on the right to activate the Remove Link button, then click the Remove Link button to remove the link.

• Click the Column Display Order button to select Database (the default) or Alphabetical.

• After completing the parent and child joining, select a radio button to identify the join type. The default is 1 to N. In this example, we have left the default at 1 to N.

• Click OK to return to the Source Hierarchical Relationships screen, which displays your table tree showing the currently defined relationships.

• Click Cancel to return to the Source Hierarchical Relationships screen without saving your entries.

• Click Back to return to the previous screen.

After completing your tables:

• Click Next to display the SQL Where Clause screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Select an insert/update mode

• Select one of the radio buttons to create an Insert or Update mode. In this example, we have selected the Insert/Update radio button.

• You may optionally select and edit a table on this screen. First select the table to edit (parent or child) and then click the Edit Selected Table button to display Select the Primary Keys dialogue.

• The dialogue will display items from the table you selected. Make your entries and click OK to save them and return to the select an Insert/Update Mode screen.

• Click Next to display the Summary screen.

• Click Back to return to the previous screen.

• Click Cancel to close the wizard without saving your entries.

Review the summary

• This step allows you to review the elements of your project before having Jitterbit automatically create all project definitions including operations, transformations, and web service calls. Take a few moments to browse through all elements of the project.

• If you need to make adjustments to your Database, click Back to return to the previous screen and make the necessary changes.

• After reviewing your Database, click Finish. The Transformations tab is displayed, and the Design pane populates with the name of your new project.

• In addition, you may view the operation by clicking the Operations tab to the left of the Transformations tab.

Review the mapping

When you click Finish on the Summary screen, the Transformations tab opens in the workspace. You can also open it at any time by selecting the name of your project under Jitterbit Connect / Database to Database in the hierarchical tree in the left pane and double-clicking.

Deploy the project

• On the heirarchical tree in the left pane under Operations, right-click on the Project just created to highlight it, and then left-click Deploy on the resulting drop-down.