Integrating data with Salesforce via SSIS and CData

• Download sample - 9.7 KB

Introduction

A common need in the enterprise world is the need to be able to integrate data between disparate systems (ERP, POS, data warehousing, etc...). Increasingly CRM systems are becoming common-place in the mix of systems needing to be able to talk to each other, and one of the largest CRM systems in the market is Salesforce. Unfortunately, Salesforce’s size and breadth of scope can make it intimidating for developers to get started, and even figuring out how to connect can be daunting if you don’t already know where to look. Thankfully, there are third party drivers such as the one provided by CData that make this task easy, and let developers focus on business logic instead of Salesforce connections and APIs.

Today we will be looking at how to integrate data with Salesforce using SSIS and CData’s SSIS driver.

Setup

SSIS

In this article, we assume you already have basic familiarity with SSIS and will not go through the setup steps for SSIS itself. We’ll be integrating Account data from a local database table in to Salesforce, and have set up the SSIS package to read the Account data as the first step.

For our sample, we’ll use the following Account table:

In this table, the Name field is the name of the Account, and the Id is the database generated Id.

Installing the CData SSIS-Salesforce driver

In order to connect to and interact with Salesforce, we’ll need to install a driver. We’ll be using the CData SSIS-Salesforce driver to accomplish this task, which you can download from their site. After downloading, simply follow the prompts to install and restart Visual Studio if you left it open.

Connecting to Salesforce

To begin, we need to first be able to authenticate against your Salesforce instance, which means we need Salesforce credentials.

Note: If you’re following along just for fun, you can sign up for a free Salesforce org from https://developer.salesforce.com/. The free developer org has lower data limits, but for our purposes is identical to most other Salesforce orgs you’ll need to connect to.

There are a few pieces of information you’ll need before you can continue further:

1. The salesforce username + password for the account you want to use as the integration account.
2. The type organization you are connecting to (Developer, Production, Sandbox, etc.)
3. An optional security token, depending on your organization’s security settings.

Gathering the username + password

This is the same as the username and password you use when you log in to Salesforce through the browser.

Gather the type of organization

A vast majority of the organizations you will have to communicate with will be one of 3 types: Production, Sandbox, or Developer. For our purposes, we only need to know if we are in a Sandbox organization. Generally you should be able to find this out by asking your Salesforce administrator, but you can also look in the setup menu to find this information:

Gathering the security token

The security token is a random string of letters, numbers and symbols that is tied to your user’s password. It is needed when accessing Salesforce through the API if your organization’s security settings have not whitelisted your IP address. Generally this value should be provided by your Salesforce administrator, but if you don’t know it (or you have a new user) you can reset it.

Warning: Resetting the security token resets it for all applications using the account. If you have other integrations using this account, do not reset the security token unless you plan on updating it in all locations.

There are two places you go to reset your security token, depending on your organization version.

Through the Setup Menu

In some organizations, resetting your security token can be done through the Setup Menu:

Through "My Settings" Menu

If you don’t see Reset My Security Token in the setup menu, then you need to go through the My Settings menu:

Regardless of which route you need to take, once you click the confirm button you will receive an email from Salesforce with your security token.

Adding the connection to SSIS

Now that we have the required information, we can create a new SSIS connection. In the Connection Managers section, add a new connection of type CDATA_SALESFORCE:

On the following screen, fill in the Authentication portion with the information we just gathered. If you do not need a security token, leave the field blank.

The "Use Sandbox" field is where we use your organization type: if you have a Developer or Production organization leave this as false, if you have a Sandbox organization set this box to true.

Leave all of the other settings as their defaults and click OK.

Mapping the data

Now that we have a connection to Salesforce established, we can begin mapping your data from the database table to the Account object in Salesforce.

To start, we need to add a new CData Salesforce Destination:

Once you have the destination established, it follows the same conventions that most data mappings follow. You can pick from the input list, and map it to a field in the output list:

Error Handling

With the mapping set up, we need to add error handling for any records that fail to be inserted. The simplest method is to log the errors to a flat file, which we’ll use for now:

Of course, since this is using the standard SSIS error redirection, you can add any logic needed by your business requirements.

Wrap Up

At this point, you have a fully functioning Salesforce connection, and can push data from your local database to Salesforce. Hopefully this walk through makes finding the connection information easy, and utilizing CData’s Salesforce driver for SSIS authentication allows for interacting with Salesforce in a straightforward manner. Using the driver makes integrating your ERP, web site, and POS systems with Salesforce easy, allowing you to keep your data in sync and ensure everyone is always on the same page.