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.

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former InfoQ.com editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.