XML to database wizard in Jitterbit Design Studio

Create an XML to database standard transformation with Jitterbit connect wizard

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.

This section of help covers the creation of XML to Database. The screen conventions in this document are: Source is on the left, Target is on the right.

Create an XML to database standard transformation

• Log in to your Jitterbit.

• To use the Jitterbit Connect Wizard, from the main menu click Help > Getting Started. The Getting Started tab appears in the right pane of your screen.

• Now click the button labeled Start the Connect^TM Wizard; OR

• Click the icon at the top of the screen to display the Jitterbit Connect menu.

  

• Start the wizard by clicking the XML to Database button in the Standard column in the Jitterbit Connect Wizard menu, which displays the Database Source Selection screen; OR

• Right-click on the desired Jitterbit Connect sub-branch in the Design pane and select New XML to Database.

• Begin creating your project with the wizard.

  Note

  If you have not logged in at the beginning of the project, you will be prompted to do so when you click XML to Database.

  

• Complete the fields as follows:

  Field	Description
  Jitterbit Server	Select the name of the Jitterbit server from the dropdown.
  User	Enter your user name.
  Password	Enter your password.
  Remember Password checkbox	Check this box if you want the system to remember your password in the future.
  Advanced link	Click the left triangle to reveal advanced link options: Use of HTTPS when communicating with the server. Selection of a port. For details, click the Help button in the lower right corner of this dialogue.

• After you have completed the fields:

  Control	Description
  Login	Click this button to login to the Jitterbit server and display the Source screen.
  Cancel	Click this button to close the wizard without saving your entries.
  Question Mark	Click the question mark to display help for this dialogue.

Create source XML structure

• Select one of the radio buttons:

  • Select an XML structure - reveals any existing XML schemas (.xsd or .dtd extension). Then highlight your selection.
  • Upload an XML structure file - allows you to navigate to and upload another DTD or Schema.

• In this example, we chose the Select an XML structure radio button and highlighted PurchaseOrder.dtd.

  Control	Description
  Next	Click this button to display the Source Select a Root screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Create source root element

• Select one of the radio buttons:

  • Select a root node from the list - reveals data nodes, and you may choose one as the root.
  • Look up the root from a sample XML document - allows you to navigate to another XML document to determine its root node.

• In this example, we choose the Select a root node from the list radio button and highlighted company_name.

  Control	Description
  Next	Click this button to display the Source Options screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

  

• The Validates XML instances against the selected XSD or DTD checkbox is checked by default. In this example, we are leaving the checkbox checked.

  Control	Description
  Next	Click this button to display the Source Select a File Source screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Create file source

• To edit an existing File Source, select from the dropdown (e.g., XML Source, Text Source, Complex Text) and click Edit. The screen expands to display the name and type of XML Source, and the Connection Parameters.

• To create a new File Source, click the Create New button and complete the fields.

• In this example, we chose an XML source from the dropdown and clicked Edit to update the Source connection parameters.

  Note

  The type of File Source selected will determine the connection parameters to enter. For details see Enabling local file sources.

  

• XML Source fields:

  Field	Description
  Name	This field automatically populates with a system-assigned name. This is an editable field where you can choose to enter any name.
  Type	Use the dropdown to select your type. In this example we have selected File3-are.

• Complete the connection parameters fields as follows.

  Parameter	Description
  Path	Enter the path where the "Get" files reside.
  Login	Enter your login and click the Password button. A dialog appears to enter your password.
  Get Files	Enter the name of the data source file. You may include wild cards to get multiple files.
  Filter Options	Click this button to open a dialogue allowing you to ignore case or use aregular expression.
  Options link	Click this link to perform refinements on the operation, i.e., deleting or re-naming a file after processing.

• After completing your entries:

  Control	Description
  Test Connection	Before clicking Next, click this button to make sure that all your connections work. A dialog box appears confirming that the connection was successful. If the connection test is unsuccessful, go back and check your parameters and correct if necessary. Click OK to return to the Source screen.
  Next	Click this button to display the Database Target Selection screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Create database target selection

• To edit an existing Database Target, select from the dropdown and click Edit. The screen expands to display the name and type of Database Target, and the Connection Parameters.

• To create a new Database Target, click the Create New button and complete the fields.

• In this example, we chose PostgreSQL from the dropdown and clicked Edit to update the connection parameters.

  Note

  The type of Database Target selected will determine the connection parameters to enter.

  

• PostgreSQL fields:

  Field	Description
  Name	This field automatically populates with a system-assigned name. This is an editable field where you can choose to enter any name.
  Type	Use the dropdown to select your type. In this example we have selected Database.

• Complete the connection parameters fields as follows.

  Parameter	Description
  Driver	Click the Select button and highlight the driver name where the database resides.
  Server Name	Enter the server name.
  Database Name	Enter the database name.
  Login	Enter your login and Click the Password button. A dialog appears to enter your password.

• After completing your entries:

  Control	Description
  Test Connection	Before clicking Next, click this button to make sure that all your connections work. A dialog box appears confirming that the connection was successful. If the connection test is unsuccessful, go back and check your parameters and correct them if necessary. Click OK to return to the Source screen.
  Next	Click this button to display the Target Table Selection screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Create target 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.

  Note

  If you have not logged in at the beginning project, you will be prompted to do so when you click the Download List of Tables button.

  

• Complete the fields as follows:

  Field	Description
  Jitterbit Server	Select the name of the Jitterbit server from the dropdown.
  User	Enter your user name.
  Password	Enter your password.
  Remember Password checkbox	Check this box if you want the system to remember your password in the future.
  Advanced link	Click the left triangle to reveal advanced link options: Use of HTTPS when communicating with the server. Selection of a port. For details, Click the Help button in the lower right corner of this dialogue.

• After you have completed the fields:

  Control	Description
  Login	Click this button to login to the Jitterbit server and display the Table Selection screen.
  Cancel	Click this button to close the wizard without saving your entries.
  Question Mark	Click the question mark to display help for this dialogue.

  

• 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, as in the example below.

  

• After completing your tables:

  Control	Description
  Next	Click this button to display the Source Hierarchical Relationships screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Create Hierarchical Relationships

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

• Click the Select Child Table dropdown 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 a parent column (OrderHeader in this example) to a child column (OrderDetail in this example) to link them. As an alternative, you may select a column in each table to activate the Link button, then click this button to link the columns.

• You may select a link between two columns to activate the Remove Link button, then click that 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

  

• After completing your tables:

  Control	Description
  OK	Click this button to return to the Source Hierarchical Relationships screen, which displays your table tree showing the currently defined relationships.
  Cancel	Click this button to return to the Source Hierarchical Relationships screen without saving your entries.

• Click the Back button to return to the previous screen.

  

  Control	Description
  Next	Click this button to display the SQL Where Clause screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

  

• In this example, we are not using filter conditions.

  Control	Description
  Next	Click this button to display the Target screen.
  Cancel	Click this button to close the wizard without saving your entries.

• Click the Back button to return to the previous screen.

Review the summary of the database

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 the project, click the Back button to return to the previous screen and make the necessary changes.

• After reviewing your project, click Finish to display the Transformations tab. The Design pane populates.

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 in the Design pane and double-clicking.) The Mappings message is also displayed above the tab.

  

• If you have left the Use Automapper checkbox checked, most or all of the fields could be mapped for you already. Valid mapping is indicated by green lines between items in the Source and Target columns and, in addition, by the presence of a blue square next to each validly mapped item name.

• A red line indicates invalid mapping.

• A yellow line indicates that Jitterbit isn't sure if the mapping is valid for all data possibilities, but will attempt the transformation with this mapping.

• You can complete the mapping on the Transformations tab by dragging and dropping from Source on the left to Target on the right. When you do so, the Target item will take on the same name as the Source item, and a blue square will appear to the left of both Source item and Target item. See Transformations for more transformation details.

• To save your transformation, click the down arrow on the Status ribbon and select Save.

• To exit this screen, click the X on the far right of the tab.

The SFDC Wizards tab also opens when you click Finish.

• Click the SFDC Wizards tab to display the Create Account screen. Here you can change configurations such as Chunking and Truncate Fields. The latter configuration will truncate fields as needed if the target field is smaller than the source field. From the SFDC Wizards tab, you can also run or test the operation.

• View the graphic summary of the entire project, click the Operations tab, or right-click the project name in the Design pane.

  

Deploy the project

• On the Design pane under Transformations, right-click on the project just created, and then left-click Deploy on the resulting dropdown; or

• Right-click Operations at the top of the Design pane and then left-click Deploy.

• The Deploy process begins sending the data to the server, and a progress message appears.

• To cancel the Deploy process, click the Cancel button, which returns you to the previous screen.

This concludes the XML to Database process. If you wish to export this project has a Jitterpak, click here.