Database to text 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 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 text standard transformation

• Log in to Salesforce via 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.

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 Text button in the Standard column of the Jitterbit Connect Wizard menu, which 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 them 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.

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

• Optional: 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 only displayed 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.

Select the target file format

• To edit an existing Text Document, select from the Drop-Down and click Edit and complete the Fields. The screen expands to display the name, type and format of the Text Document.

• To create a new Text Document, click the Create New... option and complete the Fields. The screen expands to display the fields for the name, type and format of the Text Document.

• Enter the Name of your new document.

• Select Document Type: Select the Radio button for either Simple or Complex Text Document.

• Select Document Format: Select the radio button for either Character Delimited or Fixed Field Widths.

• Create Manually: click this Button to create your Text Document manually.

• Create From File: click this Button to create your Text Document from an existing file.

• To auto-create a Text Document: click the Create Structure button and complete the Fields. The screen expands to display the fields for the Delimiter and Qualifier, and for defining Segment properties.

• Delimiter & Qualifier: select the Delimiter & Qualifier. The "Only When Needed" radio button is selected by default.

Define segment properties

To add a field to the list:

• Click New and type anName in the Field Name Column.

• Then move to Type Column to activate a drop-down list from which you may select a field type.

• The Default and Format columns become editable text windows when you click them, allowing you to add constraints to the field.

To modify treatment of a field:

• Select its Name and click the Move Up, Move Down or Delete Field buttons.

• If your target is a flat file format, you may optionally add a Field-Validation step. See Field validation for details.

After completing your fields:

• Click OK to return to the Target screen.

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

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

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

Select the file target

• To edit an existing File Target, select from the Drop-Down and click Edit and complete the Fields. The screen expands to display the name, type and connection parameters of the File Target.

• To create a new File Target, click the Create New button and complete the fields. The screen expands to display the name and type of the File Target.

• After selecting the type, the connection parameters of the File Target are displayed.

After completing your fields:

• Click this Next button to display the Summary screen.

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

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

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

• After reviewing your Database, click Finish. Both tabs open with the Transformations tab fully displayed, and 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. If you have left the Use Automapper checkbox checked, most or all of the fields are mapped for you.

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

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

Deploy the Project

• In the right-hand pane, in the Operations tab for the new operation, click the Inverted Triangle next to the Operation name, and then left-click Deploy on the resulting dropdown.

OR

Right-click Operations at the top of the menu in the left-hand pane and then left-click Deploy.

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