ETL in the Cloud with Informatica: Part 2 – Sending Salesforce.com Data to Dynamics CRM Online

In my last post, we saw how the Informatica Cloud lets you create bulk data load (i.e. ETL) tasks using a web-based designer and uses a lightweight local machine agent to facilitate the data exchange. In this post, I’ll show you how to transfer data from Salesforce.com to Dynamics CRM Online using the Informatica Cloud.

In this four-part blog series, I will walk through the following scenarios:

Scenario Summary

In this post, I’ll build the following solution.

2012.03.26informatica17

In this solution, (1) I leverage the web-based designer to craft the ETL between Salesforce.com and Dynamics CRM Online, (2) use a locally installed Secure Cloud Agent to retrieve ETL details, (3) pull data from Salesforce.com, and finally (4) move that data into Dynamics CRM Online.

What’s interesting is that even though this is a “cloud only” ETL, the Informatica Cloud solution still requires the use of the Cloud Secure Agent (installed on-premises) to facilitate the actual data transfer.

To view some of the setup steps (such as signing up for services and installing required software), see the first post in this series.

Building the ETL Package

To start with, I logged into the Informatica Cloud and created a new Data Synchronization task.

2012.03.26informatica18

On the next wizard page, I created a new connection type for Salesforce.com and provided all the required credentials.

2012.03.26informatica19

With that in place, I could select that connection, the entity (“Contact”) to pull data from, and see a quick preview of that data in my Salesforce.com account.

2012.03.26informatica20

On the next wizard page, I configured a connection to my ETL target. I chose an existing Dynamics CRM Online connection, and selected the “Contact” entity.

2012.03.26informatica21

Instead of transferring all the data from my Salesforce.com organization to my Dynamics CRM Online organization, I  used the next wizard page to define a data filter. In my case, I’m only going to grab Salesforce.com contacts that have a title of “Architect”.

2012.03.26informatica22

For the data mapping exercise, it’s nice that the Informatica tooling automatically links fields through its Automatch capability. In this scenario, I didn’t do any manual mapping and relied solely on Automatch.

2012.03.26informatica23

While, like in my first post, I chose not to schedule this task, you’ll notice here that I *have* to select a Secure Cloud Agent. The agent is responsible for executing the ETL task after retrieving the details of the task from the Informatica Cloud.

2012.03.26informatica24

This ETL is now complete.

Testing the ETL

In my list of Data Synchronization Tasks list, I can see my new task. The green Run Now button will trigger the task.

2012.03.26informatica25

I have this record in my Salesforce.com application. Notice the “title” of Architect.

2012.03.26informatica26

After a few moments, the task runs and I could see in the Informatica Cloud’s Activity Log that this task completed successfully.

2012.03.26informatica27

To be absolutely sure, I logged into my Dynamics CRM Online account, and sure enough, I now have that one record added.

2012.03.26informatica28

Summary

There are lots of reasons to do ETL between cloud applications. While Salesforce.com and Dynamics CRM Online are competing products, many large organizations are going to likely leverage both platforms for different reasons. Maybe you’ll have your sales personnel use Salesforce.com for traditional sales functions, and use Dynamics CRM Online for something like partner management. Either way, it’s great to have the option to easily move data between these environments without having to install and manage enterprise software on site.

Next up, I’ll show you how to take Dynamics CRM Online data and push it to an on-premises database.



Categories: Cloud, Dynamics CRM, Salesforce.com

5 replies

Trackbacks

  1. ETL in the Cloud with Informatica: Part 1 – Sending File Data to Dynamics CRM Online « Richard Seroter's Architecture Musings
  2. ETL in the Cloud with Informatica: Part 3 – Sending Dynamics CRM Online Data to Local Database « Richard Seroter's Architecture Musings
  3. ETL in the Cloud with Informatica: Part 4 – Sending Salesforce.com Data to Local Database « Richard Seroter's Architecture Musings
  4. Informatica Architect | JobDesk.ORG
  5. 2012 Year in Review « Richard Seroter's Architecture Musings

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: