Database to XML 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 executes 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. The screen conventions in this document are Source on the left and Target on the right.

Create a database to XML standard transformation

• Log in to Salesforce via Jitterbit.

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

OR

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

• The Jitterbit Connect Wizard menu appears.

• Start the Wizard by clicking the Database to XML button in the Standard column of the Jitterbit Connect Wizard menu. This displays the Database Source Selection screen.

• Begin creating your Project with the wizard.

Create database source selection

• From Available Database Sources, select either Create New… or an Existing Database Source.

To create a New Database Source, fill in the requested Information.

• Enter an appropriate Name for your new source in the Name field.

• Choose Database from the Type drop-down menu.

• Select the appropriate Database Driver by clicking the Select button next to the driver field. Depending upon the driver you select, different fields may become enabled or disabled.

• Enter the Database Server Name (either name or IP address) you want to access into the Server Name field.

• Enter the Name of the database you want to access into the Database Name field.

• Enter a valid Username and Password that will allow access to your database into the Login and Password fields.

OPTIONAL:

• If the database server is listening to a non-default port, you can un-select Use Default Port and specify a Custom Port. The default port differs depending on the selected driver.

• Select the Transaction Isolation Level from the drop-down. The options are the standard Transact-SQL's. Driver specific transaction isolation levels are not supported. The default is to use the driver or database server default transaction isolation level.

• If you want to set a limit for how long to wait for a successful connection to the database, enter the Number of Seconds in the timeout field.

• If you are using a JDBC driver, you can set the Fetch Size Property. This property gives the driver a hint as to the number of rows that should be fetched from the database when more rows are needed. This setting can be used for more efficient processing for large data sets.

• If there are any additional parameters that you want to add to your database connection string, enter the Parameters in the Additional Connection String Parameters field.

• The Construct Connection String Manually checkbox is unchecked by default. If you would like to define it yourself, check this Checkbox. You must then enter the Connection String in the corresponding text field. For more information, see Manual connection string.

  • You may wish to test your Connections before clicking Next. Clicking this Button makes sure 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. You may also contact the Jitterbit Help Desk.
  • Click the Next button to go to Table Selection.

Create table source selection

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

OPTIONAL:

• You may enter a Search String in the Filter Search window. Check the Table or View Checkboxes to limit the objects to select.

• You may select the Checkbox for including system tables in the downloaded table names.

• 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.

• To remove tables from the Selected Tables column, highlight the Tables to be copied in the Selected Tables column, and click the Left Arrow.

• The selections are then moved back to the Available Tables column.

After completing your tables:

• Click the Next button to display the Source Hierarchical Relationships screen, if the selected tables have a hierarchical relationship. Otherwise, the options SQL Where Clause screen will be displayed.

• Click the Cancel button to close the Wizard and return you to the Getting Started tab without saving your entries.

• To return to the previous screen, click the Back button.

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.

• 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 One or More. In this example, we have left the default at One or More.

• Click OK to return to the Source Hierarchical Relationships screen, which displays your table tree showing the currently defined relationships OR click Cancel to return to the Source Hierarchical Relationships screen without saving your entries.

• To return to the previous screen, click the Back button.

• Click the Next button to display the optional SQL Where Clause screen.

• Click the Cancel button to close the Wizard and return you to the "Getting Started" tab without saving your entries.

• To return to the previous screen, click the Back button.

Create source SQL WHERE clause selection (optional)

• Enter any SQL Where Clause desired to filter the source data.

• Click the Next button to display the optional Primary Key Selection screen.

• Click the Cancel button to close the Wizard and return you to the Getting Started tab without saving your entries.

• To return to the previous screen, click the Back button.

Create source primary key selection (optional)

• Select a Primary Key by clicking the Checkbox in the Select column to the left of the Name.

• The Nullable checkbox is checked by default. Leave selected if you want a column to contain null data elements.

• Click the Unselect All button to remove your selections. You may then make new selections.

• Click OK to save your selections and return to the Insert/Update screen, which now displays your selections.

• Click Cancel to discard your selections and return to the Insert/Update screen.

Update the source data field selection (optional and is displayed only if the transformation is to a flat target.)

• Select a Field in the Source Data to update when you want to filter out rows that have already been transformed.

• Click the Next button to display the Target File Format selection screen.

• Click the Cancel button to close the Wizard and return you to the Getting Started tab without saving your entries.

• To return to the previous screen, click the Back button.

Create the target

• This screen prompts you to generate an XSD (an XML schema) based on the Source table.

• Click the No button to decline.

• Click the Yes button to generate an XSD. The screen expands to display a name field and Generate XSD button.

• Enter an XSD File Name and click the Generate XSD button. The screen expands to display the XML schema document.

• You may click the Options button to specify options for root name, target namespace, and inclusion of default values.

• Click the Next button to display the Target Options screen.

• Click the Cancel button to close the Wizard and return you to the Getting Started tab without saving your entries.

• To return to the previous screen, click the Back button.

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

  Note

  If you wish the option of generating an xsi:schemaLocation attribute in the target XML, enter a Schema Location in the text field. In this example, we are not selecting this option.

• Click the Next button to display the Select a File Target screen.

• Click the Cancel button to close the Wizard and return you to the Getting Started tab without saving your entries.

• To return to the previous screen, click the Back button.

In this example, the MyXML Target was previously created outside the Wizard operation and appears here.

• To select an existing File Target, click the Dropdown and make your selection.

• To edit an existing File Target, select from the Dropdown, which will open a dialog box in which you can edit the target parameters.

• Click the Create New button and complete the Fields to create a New File Target.

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

Review the summary of the operation

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 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 Transformation tab. The Design pane populates.

Review the mapping

• When you click Finish on the Summary screen, the Transformations tab opens in the workspace. The Mappings message is also displayed above the tab.

• If you have received this message, click Close. Before closing, you may also select the Do Not Show Me Again checkbox.

• 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 view the graphic summary of the operation, double-click the Operation Name under Jitterbit Connect / Database to XML in the left-hand pane.

Deploy the project

• At the top of the left-hand pane, right-click on the Project Name to highlight it, and then left-click Deploy on the resulting dropdown.

• The options for deploying the project will be displayed.

• Click the Deploy button to deploy your project.

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

• To run this progress message in the background, click the Run in Background button.

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