This article will show how to connect source and target through the framework described earlier.
We need to define 3 connections, Legacy, Staging and Salesforce.
Informatica Cloud is not completely straightforward to setup with your environment, but then again not too difficult that a day of infrastructure configuration wouldn’t fix. You download a piece of software called ‘Secure Agent’ that will run the ETL process. It is worth noting that you can only have one Secure Agent for each ETL, so both source and target data source as well as Informatica Cloud itself needs connectivity from the server running the Agent. I also found that quite a bit of time was needed to ensure all database connections were running smoothly.
Setting up a mapping configuration is quite straightforward. The tool will determine the sources and target schemas, and allow some transformations in the middle. Here I am joining the email and phone tables with the contacts before inserting into Staging.
This is a good example of a compromise where I am just taking the first email address. In practice you may want to consider how to deal with all those extra addresses.
(If you were doing this for real, then it would be better to create views within the source SQL Server and just export out of those. There would be less scope for an ETL configuration error.)
You can then just keep running until you get the migration result that you are looking for. Ensure that the ‘Truncate’ option is turned on.
Check everything is looking good in the Staging (using a SQL query):
And then define a simple ETL for loading into Salesforce. Given that the Staging should be more or less how you want things in Salesforce this should be easy:
Run and check out the new Contacts in Salesforce: