Migrating account data from a flat file to Salesforce.com using IBM App Connect Professional

This tutorial illustrates how you can use IBM App Connect Professional on Cloud to migrate customer data from a flat file (a CSV file) to Salesforce.com’s cloud offerings.

Introduction

This tutorial provides an example of cloud data migration, using IBM App Connect Professional on Cloud to migrate customer data from a flat file to Salesforce.com’s cloud offerings.Migrating customer data from a flat file to Salesforce accounts

In many cases, in a move from proprietary applications to SaaS and cloud applications, you need to migrate data from your proprietary applications to the cloud applications. Many proprietary applications use flat files to exchange data with other applications. Most ERP applications also support flat file imports/exports.

In this tutorial, you use IBM App Connect Professional’s configuration-not-coding approach and exploit a pre-built Template Integration Project (TIP) that encapsulate this specific integration use case and includes preferred practices.

Learning objectives

  • See how easy it is to migrate data from a CSV file to Salesforce.com Accounts
  • Understand how to create an integration project from a template
  • Get a meaningful experience of using the web management console and Studio of IBM App Connect Professional

Expected results

A Salesforce Account record is created for each row (of customer account data) in the CSV flat file submitted to IBM App Connect Professional.

Time required

This tutorial should take approximately 30 minutes to finish. If you explore other concepts related to this tutorial, it could take longer to complete.

Audience

Developers of integration solutions interested in using IBM App Connect Professional for data migration.

Skill level

Any

Conventions and capabilities used in this tutorial

Concepts used:
To use flat files in App Connect Professional integrations the file must be described by a Flat File Schema. In this tutorial, the schema is created by the template used. The Transform Read Flat File activity parses the schema to transform data from string to a structured representation.
Template used: T0041 – Flat File to Salesforce Accounts
This TIP receives a CSV file from an HTTP Post and creates Salesforce.com Accounts.
Activities used:
  • HTTP Receive Request
  • Transform Read Flat File
  • Try…Catch (to report any failure to insert a record into Salesforce.com)
  • If Then…Else (Used to add Business Rules or Business Logic)
  • Salesforce – Create Objects

Online resources

Tip: If you have difficulty with the resource links, open the following address in your
web browser to browse and select files: ftp://public.dhe.ibm.com/software/integration/hybrid_integration/appconnect/

Requirements

Before starting this tutorial, ensure that you have the following requirements ready:

Requirements detail
  • IBM App Connect Professional.

    This tutorial was prepared using the free full-featured IBM App Connect Professional trial. Try IBM App Connect Professional for free.

  • A Salesforce.com account that is to be used to read account data from Salesforce.com and to create and update account data in Salesforce.com.

    This tutorial was prepared using a free Salesforce Developer account.

    Before starting this tutorial, note the username, password, and security token of the Salesforce.com account.

    Tip: The security token is needed because connectivity is provided through the Salesforce.com API. For information about finding your Salesforce.com security token, see Reset Your Security Token in the Salesforce help.
  • Use a web browser such as Internet Explorer, Mozilla Firefox, Apple Safari, or Pale Moon to log in to App Connect Professional.

    In this tutorial, you use the Studio (a Java Web Start application) of App Connect Professional to edit an integration project. To run a Web Start application (like Studio) from your web browser, Oracle recommends using a web browser such as Internet Explorer, Mozilla Firefox, Apple Safari, or Pale Moon. Alternatively, you can download the Studio JNLP file to your computer and then run that file locally with the Java Web Start application (javaws) of the latest java version installed on your computer. For more information, see the blog post Java Web Start in or out of the browser.

    Tip: To run Studio as a Java Web Start application from your web browser, ensure that you have selected the Java option “Enable Java content in the browser” and added the following site to the exceptions list: https://castiron.ibmcloud.com/

Summary

In a matter of minutes, you create and run an integration project in IBM App Connect Professional that migrates customer data from a flat file on your computer to create Account records in Salesforce.com.

  1. Use the web management console to create an integration project from the template.
  2. Open the project in the Studio to make a few changes to the configuration of your project.
  3. Deploy the project to run in IBM App Connect Professional on Cloud.
  4. Use App Connect’s HTTP Post Utility, or another utility like Postman, to submit a CSV file of customer data to be migrated.
  5. Using a web browser, login to your Salesforce.com account and check the Accounts that have been created from the migrated data.

Creating the project in the web management console

You first create an integration project from a template in the web management console and use a wizard to configure most of the properties required for the project.

Procedure details
  1. Log in to IBM App Connect Professional: https://appconnect.ibmcloud.com/professional/

    Tip:To be able to run the Studio from your web browser, log in using a web browser such as Internet Explorer, Mozilla Firefox, Apple Safari, or Pale Moon.

    This displays the web management console, showing the tabs for Create, Modify, and Run; the three main steps to get your integration solution running.

    Figure 1. The web management console after logging in to IBM App Connect Professional on Cloud
    acpro_webconsole_start
  2. In the web management console, click the Create tab. You use the Create tab to select a template to create an integration project or create a project from scratch.

    In this tutorial, we want to find and select the template to migrate a flat file (a CSV file) to Salesforce.com. For example:

    1. Expand the Target Endpoint drop-down menu
    2. Type s and then scroll down to and select salesforce.com.
    3. Select Search (next to the Target Endpoint drop-down menu).

      This displays a table of templates for Salesforce.com.

    4. Optional: Select the header cell of the Source column, to sort the table alphabetically.
    5. Scroll down and then select the template T0041 – Flat File to Salesforce Accounts

      The template row indicates that the TIP is for flat file to Salesforce, that the integration project uses HTTP methods (rather than FTP, say) and that it is a certified by IBM as depicting a common use case and using preferred practices.

      Figure 2. Web management console, selecting the T0041 template to create a new project
      acpro_webconsole_t_endpoint_5
    6. Select Create a project based on this template.

      After a short while, the TIP configuration wizard is displayed.

      Figure 3. The TIP configuration wizard, as first opened
      acpro_webconsole_tip_config_wizard_1
  3. On the wizard Introduction page, click Next.
  4. On the Edit Configuration Properties page, enter the credentials for your Salesforce account.

    The Salesforce.com password is the concatenation of the account password and a security token; for example, for password mysfpwd and security token 1AB2CdEfg3HijkLM4NOpqrsTu, the password to enter would be: mysfpwd1AB2CdEfg3HijkLM4NOpqrsTu

    Click Next.

  5. On the Verify Salesforce.com connectivity page, click Test Connection to verify that IBM App Connect Professional can connect to your Salesforce.com account.

    This page provides options for your to customize the Salesforce.com connectivity, if you need to.

    Figure 4. The TIP configuration wizard, Verify Salesforce.com connectivity page
    acpro_webconsole_tip_config_wizard_3

    Click Next.

  6. On the Get custom fields page, leave the default settings.

    On this page, you have options to change the selection of target fields that you want available later to map for data to be migrated from the source file. You can also change the type of Salesforce object that the data is to be migrated to.

    Click Next.

  7. On the Update the flat-file fields page, leave the default selections.

    On this page, you have options to change the properties and delimiters for the source fields of the flat file, and to test source data to be migrated.

    If you want to examine the properties and delimiters, select Customer or any of the field names in the Schema Layout pane. For example, by default each Customer in the CSV file is on a separate line, delimited by Line End (CRLF), and each field within a Customer is separated by the ‘,’ (comma) character delimiter.

    Click Next.

  8. On the Map Account fields page, leave the default mappings.

    On the Map Account fields page, you may update the map used to migrate data from the flat file to the Salesforce Accounts. For example, if you had added custom fields to the flat file, you can drag from the name of each custom field to the name of the Salesforce Account field that you want data migrated to.

    Click Next.

  9. On the Sample Input Data page, copy the sample data and then save it into a CSV file (for example, customerdata_accounts.csv) for use later to test the integration project.

    Click Next.

  10. On the Verify Integration page, click Save.

    The project is added to the Modify tab, which is opened automatically.

    Figure 5. The TIP configuration wizard , new project opened in the Modify tab
    acpro_webconsole_projects_new_project_crop
    Hint: In this example, the project was saved with a new name, CSVFlatFiletoSalesforceAccounts, because a project with the default name already existed in the web management console. (If you are not offered the chance to save the project with a new name, you can save the project with a new name when editing in the Studio.)
    Tip: Before we can verify this integration project, or publish it into production to run in App Connect Professional on Cloud, we need to edit the project in the Studio to change a few configuration options.

Editing the project in the Studio

To enable a project to be run in App Connect Professional on Cloud, you use the Studio to select the option to run in the cloud and note the URL for the project that you use to interact with it.

Procedure details

About this task

The web management console is convenient for creating your first integration project, or the initial state of other projects, but there is more that you can do in the Studio to create and customize your projects.

For example, before you can run your new project in App Connect Professional on Cloud, you use the Studio to select the option to run in the cloud and note the URL for the project that you use to migrate data to Salesforce. You can also customize the URL and other aspects of the project.

Procedure

  1. In the web management console, in the Actions column for the project, select Edit Project in Designer.

    This downloads and runs the App Connect Studio launcher application (JNLP file) to start the Studio as a Java Web Start application. There is some delay as the launcher application and Studio are loaded and initialized.

    During the download, if you see a Java security request to run the App Connect Studio launcher application, select the option to run the application.

    Tip: After the first time that you launch the Studio from the web console, you can launch the Studio by opening the downloaded JNLP file, or again from a project’s Edit in Designer action of the web management console.
  2. When the Studio has downloaded, it opens the wizard to configure the project.

    Most pages of the wizard are the same as the wizard used in the web management console to create the project.

    Figure 6. Studio, project configuration wizard
    acpro_studio_cfgwiz_1

    In this tutorial, the wizard is only used to import the project into Studio, where the project is to be configured.

    Select Next.

  3. On each page, select Next, until you see the Publish Project page.
  4. On the Publish Project page, select Finish.

    This displays the Finished page, where you can publish a review of thisTIP. You can do that at a later time.

    Select Close.

    The project is opened in the Studio to show its orchestration (flow).

  5. Set the option to run the project in the cloud, and determine the URL to be used.
    1. Edit the HTTP Receive Request node; for example, select the node on the palette.
    2. On the Configure view, note the value of the parameter URL (Path After Hostname); by default, kick_off.

      This is the unique value used to invoke this project when published. Optionally, change the value; for example, to: csv2accounts.

    3. On the Pick Endpoint view, click Edit.
    4. In the Edit Endpoint window, in the Location section, select the check box Configure for App Connect on Cloud and note the hostname and port to be used later: https://provide.castiron.ibmcloud.com:443
    5. Click OK.
  6. Validate the project by selecting Orchestration > Validate, or by selecting the Toolbar ‘tick’ icon: validate_tool_icon

    You should see the success message The Studio Project is Valid.

    Close the message window.

  7. Save your project to the cloud.
    1. Ensure that you are still logged in to App Connect Professional on Cloud. If you see a Login link at the bottom of the Studio, select the link to log in.
      Figure 7. Logging in to the Studio after a period of inactivity
      acpro_studio_loggedout_in_crop
    2. Select File > Save Project (To Cloud) or select the Toolbar ‘Save’ icon: acpro_studio_save_icon
    3. Optional: You can now close the Studio.
  8. In the web management console, publish the project; select the Publish Project icon: acpro_webconsole_publish_icon
  9. Select the Production tab, to show the project configuration listed.
  10. To run the project configuration, click the Run Configuration icon: acpro_webconsole_run_config_icon

    The project configuration is now running and ready to accept requests to migrate data. You can use the dashboard of the web management console to manage use of the project configuration, such as to browse the summary, results, and any alerts.

    Figure 8. New project configuration running in the web management console
    acpro_webconsole_run_config_2

Using the integration project to migrate data to Salesforce

The integration project can be used to migrate data from a CSV file to create Salesforce.com Accounts.

Procedure details

About this task

To test the data migration, you can use App Connect Professional’s HTTP Post Utility or another utility like Postman, to submit a CSV file of customer data to be migrated.

For the CSV file, you can use the sample data that you saved earlier when creating the project, or use some other CSV file that has customer data with the following syntax:

name, address1, address2, city, state, zip, country, phone, fax
For example:

John Doe1, 2593 Coast Ave, #200, Mountain View, CA, 94043, USA, 666-666-7777, 666-666-7777
John Doe2, 2593 Coast Ave, #200, Mountain View, CA, 94043, USA, 666-666-7777, 666-666-7777
John Doe3, 2593 Coast Ave, #200, Mountain View, CA, 94043, USA, 666-666-7777, 666-666-7777
John Doe4, 2593 Coast Ave, #200, Mountain View, CA, 94043, USA, 666-666-7777, 666-666-7777
Hint: Note the blank line after the last customer data line. The integration project sees each Customer data item as a separate line delimited by Line End (CRLF), so to migrate the last customer data line that line must end with CRLF.

Procedure

  1. Using a web browser, login to your Salesforce.com account and check the Accounts that have been created from the migrated data.
    1. Log in to your Salesforce.com account, and then select the Accounts view.
    2. Select a view for new accounts from the drop down menu (for example, New This Week), and then click the refresh icon to ensure that the view is current.

      Your list of accounts should not show any of the test records to be used (JohnDoe*).

      Figure 9. Salesforce Accounts view, not showing any of the test records to be added.
      sf_accounts_0
  2. To use your integration project with the Postman utility, startPostman.
    1. Select the POST method.
    2. Enter the URL string containing the App Connect Professional hostname and port that you notedwhen configuring the project in the Studio. Append the environment value, Deployment, and the URL parameter (for example, /csv2accounts) that you configured earlier to be used to invoke the project.
      The URL, should be like the following:

      https://provide.castiron.ibmcloud.com:443/env/Production/csv2accounts
    3. Select the Authorization tab, choose Basic Auth, and then set Username and Password to thevalues for your IBM ID.
    4. Select the Body tab, and then select the binary check box.
    5. Click the Choose Files button, browse to and select your CSV file (forexample, customerdata_accounts.csv), and then click Open.
    6. To invoke the integration project, to migrate the customer data from the flat file, click Send.

      If you have done everything right, you get a 200 success message in Postman.

      Figure 10. Postman utility, success messages after sending a CSV file of customer data to be added to Salesforce.
      acpro_postman_b4post
    7. In Salesforce, refresh the Accounts view.

      You should now see the Account records created in Salesforce, for customer data migrated from the CSV file by App Connect Professional.

      Figure 11. Salesforce Accounts view, showing the Account records created for customer data migrated from the CSV file by App Connect Professional.
      sf_accounts_postsuccess
    8. Optional: To browse any of the Accounts, select the Account Name link.
      Figure 12. Salesforce, details view of an account created for migrated data.
      sf_account_johndoe1

Conclusion

In a matter of minutes, you created and ran an integration project in IBM App Connect Professional to migrate customer data from a flat file on your computer to create Account records in Salesforce.com.

  1. Used the web management console to create an integration project from a template.
  2. Edited the project in the Studio to make a few changes to the configuration of your project.
  3. Deployed the project to run in IBM App Connect Professional on Cloud.
  4. Used the Postman utility, to POST a CSV file of customer data to be migrated through App Connect Professional.
  5. Used a web browser to log in to your Salesforce.com account and checked that the Accounts were created from the migrated data.

This completes the tutorial.

Join The Discussion

Your email address will not be published. Required fields are marked *