Salesforce query wizard in Jitterbit Design Studio

Create a Salesforce query operation

This page described how to create a Salesforce Query operation using the Jitterbit Connect Salesforce Query Wizard.

Start the wizard

• Click the icon at the top of the screen to display the Jitterbit Connect dialog window:

  

• To start the wizard, click the Salesforce Query button in the Jitterbit Connect Wizard menu, which opens the wizard.

Salesforce login

• If you have already created a Salesforce org login, connection details will appear in the Query wizard. You may wish to click the Test Salesforce Login button to check the connection.

  

• If you have not yet created a Salesforce login, or you need to use different credentials, select Create New… from the dropdown menu below Select or create a Salesforce Org.

• Note that any changes you make in this page of the wizard will be automatically saved to the Salesforce Org shown on the page. See the Salesforce org page for details.

• On the Salesforce Login screen, click Next to move to the next screen. (If required, you can click the Back button to return to the previous screen in any of these screens.)

Select the Salesforce object

• On the Object screen you specify the Salesforce Object that the operation will use.

• Simplify your view by typing the first few letters of an object's name in the Filter text entry box, or click the Objects to show dropdown menu and select from either Standard Common Objects, All Custom Objects, or All Objects.

• Select the desired Salesforce Object. In this example, acc has been entered in the Filter box and Account selected.

  

• Click Next to move to the Source screen.

Build your Salesforce query

• On this screen, you begin building the query. You can create either a Simple or Relationship Query:

  

• For a simple query (all references in a single table), use the Simple Query pane on the left and check the Items to be included in your query. As you do so, they will be added to the Query string in the right pane. Using the Filter text entry box, the Select All, and Remove All buttons can help you quickly specify each field.

• For a relationship query (which includes references to other tables, either parents or children of the current object), click the Relationship Query tab on the left. Lists for Parent and Child relationships appear at the bottom of the left pane:

  

• Select an object from the Child relationships list and double-click it to view its Fields in the object. You can add them, as shown in this example, where fields from Contacts have been added (LastName, Department, Email, etc.):

  

• Check a field in the child table to add them to your query string. Again, you can use the Filter text entry box to quickly find desired items.

• To move back to an earlier table (such as Account, as shown in the above example), click on the table name in the breadcrumbs of the wizard.

• You can add conditions to the query using the Field, Operator, and Value controls under Conditions; for example, AccountSource = 'SF'. When you have completed the entries in these controls, click Add Condition and the condition will be added to the query string:

  

• You can click the Test Query button on the lower right to validate the query.

• After completing the query, click Next to move to the Target screen.

Define the target

Select where you want to put your data

The target is the destination of the data retrieved from the Salesforce Query. Select one of:

• An existing target type from the dropdown menu, such as Local File, FTP Site, File Share, Temporary Storage, Database, HTTP, or LDAP
• Click the Select or Create New button to either select an existing target or to create a new Target of the currently selected type.

For details on the different targets, see the documentation on Targets.

Local file targets

In the case of a Location File Target, you can directly give a filename of where you want the data stored.

• Specifying a Local File as a target and giving its location:

  

• Clicking Next will take you to the Select A File Format screen, where you specify either to create a file format based on the fields in the query (optionally with the field names as the first line of the file), to select an existing file format, or to create a new file format:

  

• Specifying a Local File as a target, and selecting the using of an existing Local File target:

  

• When you have completed your entries for the target, click Next to move to the next screen.

Select a file format

• Once you have specified the target, you will need to specify the Target File Format. If you have selected a database target instead of a file target, see Using a database as a target.

  

• Select a Radio button to either create a file format from the fields in your query, select an existing File Format, or create a new file format.

• If you use either an existing File Format or create a new one, you will be prompted at the end of the wizard to Select or Create New in the dropdown.

• Click Next.

Schedule and options

• Creating or setting a schedule is both optional and can be done at a later time. For more details on scheduling, see Create a schedule.
• You can edit an existing schedule by opening it in the Schedules folder of the Project Items panel and making any desired adjustments.
• You can use this wizard screen to either add scheduling to an unscheduled operation or open the operation under the Project Items Panel and make the appropriate adjustments.
• Set the Occurrence, Daily Frequency (hours or minutes) and Duration of the operation.
• When setting Duration, be aware that Starting at and Ending at dates in a schedule are inclusive.
• To display these options, open the disclosure triangles besides Occurrence, Daily Frequency, and Duration.

• When finished, click Next to move to the Summary screen.

Summary screen

On the Summary, you can edit the Name given to the wizard and specify whether to use the wizard's Automapper to map the data:

• Click Finish to complete the wizard.
• If you need to create or approve a transformation mapping, you will be taken to that screen.
• Otherwise, the design items for your operation will be created and become visible in the Project Items panel on the left side of the screen and you will be taken to the operation's Salesforce Query page.

Transformation mapping

If you chose Select an existing File Format (or created a new one) in the Target screen of the wizard, the transformation mapping appears in your work area:

• If you specified the *Use Automapper *checkbox, most or all of the field mapping may be completed. Complete any manual mapping as appropriate.
• A mapping is shown as a line between items in the Source (left) and Target (right) columns.
• A green line indicates a valid mapping and is further indicated by the presence of a blue square next to each validly mapped item name.
• A red line indicates an invalid mapping.
• A yellow line indicates that a mapping is of questionable validity; however, the operation will attempt the transformation with that mapping.
• You can complete the mapping on the Transformation tab by dragging and dropping from the Source on the left to the Target on the right. When you do so, and a valid mapping is the result, a blue square will appear to the left of both the Source and Target items.
• To save your transformation, click the down arrow on the Status ribbon and select Save.

Complete the mapping as required and click Finish to complete the wizard and the creation of the design items.

Completed Salesforce query

• Once completed, you will be taken to the summary screen for the new Salesforce Query.
• You can return to this summary page by finding its operation name under Jitterbit Connect in the Project Items panel.
• You can modify any of the design items in your project such as Salesforce Login, Salesforce Query, Mappings, Write data to, Operation, and Run on Schedule by clicking their adjacent Edit button.
• The Run Query, Test Query, and Delete button functions are described below in Deploy the project and run the operation.
• Advanced Options include operation options such as chunking and field truncation. They are covered below in Advanced options.

Advanced options

These advanced options are available for a Salesforce Query:

File options

• Write as UTF-8: Select to write the target file as UTF-8 character encoding.
• Target header: Select from one of No Header, Salesforce Field Names, or File Format Field Names.

Operation options

• Include deleted rows: Select to included deleted rows in the output of the query.
• Batch size: By default, this is set to 2000 records.
• Retry Policy: Displays a dialog to set the retry policies. See the Salesforce advanced page.
• Chunking: By default, chunking is not enabled for Salesforce Queries. It can be enabled in the operations options, as described on the Salesforce advanced page.

Additional operation options for chunking and writing success and failure records are described on the Salesforce advanced page.

Deploy the project and run the operation

Deploy the project

Deploying a project before running is not required as it will automatically be deployed if needed.

To deploy the project, either:

• Click the Deploy icon on the toolbar; or

• On the Project Items panel right-click on the Name of the project just created, and then left-click Deploy on the resulting dropdown menu; or

• Right-click Operations** at the top of the Project Items panel and then left-click Deploy on the resulting dropdown menu.

The project deploy process begins sending the project to Harmony, and a progress message appears.

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

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

The operation is now complete and deployed.

Run the operation

This image shows the buttons available on the Salesforce Wizard summary pages. The labels (such as Run Query or Run Insert) will vary depending on the wizard that you are using. The Result History button is not available on the Salesforce Queries summary page and the Test… button is not available on the Salesforce Bulk Processes summary page.

• Press the appropriate Run… button to place the operation in the queue for execution.

  • If the project has not already been deployed, or has been changed since the last deploy, it will automatically be deployed. See Create an operation for additional information on creating, deploying, and running operations.

  • The Operation Monitor displays at the bottom of the screen tracking the status, time received, time started, and the time the operation completed processing.

  • When the operation completes processing, the Result popup displays the time of completion, the status, the number of records that successfully processed, and the number of records that failed to process. The View success file and View failure file buttons are described below.

  • Press the Test… button to test the operation. This option is intended to test the operation only in an environment that is connected to a Salesforce Sandbox and should not be used in a production environment. Selecting Test Insert on the Salesforce Inserts summary page, for example, processes and inserts the first 200 records into the Salesforce Sandbox. Note: The Test… button is not available on the Salesforce Bulk Processes summary page.
  • Press the Result History button to display a popup list of recent activity including the time completed, status, the number of records processed successfully, and the number of records that failed to process. Select a result in the list to enable the View success file and View failure file buttons, which are described below. Note: The Result History button is not available on the Salesforce Queries summary page.
  • Press the Delete button to delete the operation from the project.
  • The Confirm delete popup window displays. Click Delete to confirm and delete the operation.
  • The Delete Wizard Items? popup displays the associated operation components.
    • Select Yes to delete the associated wizard objects in addition to the insert operation.
    • If you select No, the wizard objects remain available within the project. NOTE: Only the insert operation is deleted.
    • Selecting Cancel cancels the deletion of the insert operation and the associated wizard objects.

See Create an operation for additional information on creating, deploying, and running operations.

View success and failure files

Viewing success and failure files is available if you are running operations on a private agent. The buttons View Success file and View failure file are available after you press the Run... button or the Result History button as described above and then select one of the results. This is an example of the Result History popup window, showing the buttons and a selected result:

• View success file: If you are running the operation on a private agent, selecting the View success file button displays the first 100 records that successfully processed within a popup window.

• View failure file: If you are running the operation on a private agent, selecting the View failure file button displays the first 100 records that failed to process within a popup window.

• In this example, three records are shown:

  

  • Copy File To...: This button saves the success or failure records to a CSV file. The default file name is salesforce.csv.

  • Open External Program: This option is currently not available.