CRM Data Migration Part 4: Connections with Informatica Cloud

CRM Data Migration Part 4: Connections with Informatica Cloud

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.


Screen Shot 2016-04-26 at 7.07.03 PM

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.

Screen Shot 2016-04-26 at 7.10.31 PM

Check everything is looking good in the Staging (using a SQL query):

Screen Shot 2016-04-26 at 7.16.48 PM.png

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:

Screen Shot 2016-04-26 at 7.21.04 PM.png

Run and check out the new Contacts in Salesforce:

Screen Shot 2016-04-26 at 7.25.33 PM.png




CRM Data Migration Part 3: Framework

CRM Data Migration Part 3: Framework

In 2016 most new CRM installations are Cloud based, including, MS Dynamics Online and Oracle Sales Cloud.

There are many options for tools that will move data into both the Staging area and the CRM. For the purpose of this article I will use Informatica Cloud.


Performance depends on a lot of variables such as the speed of your internet pipes, servers, types of cloud systems used, etc.

It is worth noting that considerable data migration may cause a significant impact to your framework. For example you may need to move a copy of your data to an area that can reach through your DMZ. How long will it take to copy 10k records over to your staging environment? How long will it take to copy those fixed records into your new Cloud platform? How long can you take your CRM system offline during migration?

On-Premise Approach

You can setup your staging environment and ETL tools on-premise. This has the advantage that you have some control over environmental variables. Remember that you may not have total control, which could prove signifiant. What if they are other ETL processes running overnight that use up all the local bandwidth you were looking for in your migration?

Cloud Approach

Although you lack some of the direct control that on-premise offers, you do nevertheless have a more reliable environment that is dedicated to you and segregated from other services running on the cloud platform.

To keep with the Cloud theme I will also use a Microsoft Windows Server with SQL Server as a Microsoft Azure VM for the Staging database. Any other cloud (or on-premise) database solution such as AWS would work just as well, as well as a solution leveraging the Azure SQL Database Service.


Given that you can get free trials of most Cloud software, you can actually setup a ‘proof of concept’ and seeing how it works for you quite quickly. In my example this applies to Microsoft Azure, Salesforce and Informatica Cloud.

One benefit of the Cloud based approach is that you can provision your framework just for the duration of the migration. When the migration has been completed and signed-off then you can just turn it off and stop paying for it.



CRM Data Migration Part 2: Scenario

CRM Data Migration Part 2: Scenario

In order to illustrate how a migration will work, I have decided to migrate my (hypothetical) sports business from a custom solution based on Microsoft SQL Server to

Luckily Microsoft provide a good example of a legacy system : Adventureworks! You can download the database, attach it to whatever SQL Server instance you have (even the free Express edition) and treat that as your legacy source.

The great thing about the AdventureWorks is that it illustrates very well how differently two CRM systems can treat a customer. This is the data model of ‘Person’ in AdventureWorks, which we will have to migrate to’s ‘Contact’ object:


We can already see that a good amount of the data structure is not required. We don’t have to migrate the data in ‘StateProvince’ and ‘CountryRegion’ because it already exists within (although the values will need to be matched still). The ‘Password’ table should almost certainly not be migrated, although it leaves a tricky question about how to bring that (presumably) web site security login functionality to the new system. Many aspects are highly denormalized (limitless phone numbers, email addresses, etc).

Basically a simple mapping will not be possible. The flexibility of the relational database will make your migration task difficult. It is important to determine what data is truly important to you.

CRM Data Migration Part 1: Conceptual

CRM Data Migration Part 1: Conceptual


So you’ve decided to invest in a brand new CRM system. Given that most CRM systems are customized to some degree, moving data from any source to target system has many issues.

Please note that these articles are only about data migration. CRM system design is a different subject altogether.

This is also not a step by step guide. It is really just to describe some (and not all) of the challenges that you may face, as well as some examples to show how a migration framework could work.

Migration frameworks cannot be purchased off the shelf or downloaded, but rather a combination of working out an approach that works for your business scenario and matching that with the appropriate technologies that address that approach. You need to consider a good number of aspects before you even begin to think technically about it. The conceptual areas below may help with this.


1. High Level Steps

Conceptually you can have three steps:


Step 1: Source can be any data repository you have. An Excel spreadsheet, an existing CRM system, a data warehouse, etc. Additionally you can have multiple sources required for a migration.

Step 2: Stage is where you consolidate, massage, enhance and prepare your data sets for loading.

Step 3: Target is where the data ends up

It is worth noting the Step 2 can be optional. If your source data is of acceptable quality, your transformation requirements are simple and your ETL tool has enough functionality then you could just go from Source to Target.

2. Master Data

Does your source CRM contain all of your master data? What if it needs to be combined with other data such as a separate system that manages products? What if other departments in your company want to maintain their own master lists of customer information?


3. Multiple Source Systems

It is common to merge multiple systems together into the new CRM. For example your company has acquired another, and you want to use this opportunity to consolidate your customer systems. You will need to think of merging rules, such as what to do when the same distinct customer exists in both systems and which data come first? Do you accept that the ‘losing’ data source will not be used?


4. Change Deltas / Cutover Plan

Data may not always migrate cleanly in a CRM system. For example, an Opportunity may be in an open state and be waiting for further workflow actions. Is it a good idea to move it now or wait for it to close? Moving it now is the quickest way forward, but may lose your business valuable opportunities if now migrated correctly. Waiting for it to close means maintaining your old CRM system and then migrating the delta, which may itself be complicated to work out (i.e. a Contact details could be updated in both old and new systems whilst the related Opportunity remains open. Which has primacy?).


5. Difference in Data Structures

Many consultancies will direct you to not consider the design of your old CRM system when building your new CRM system, but rather ‘focus on the business outcome’ desired for the upgrade. This is all fine in theory, but legacy data structures were created for a reason (even bad ones) and you will probably find that you will either have compromise the amount of data each structure brings across, or invest heavily in ETL techniques to achieve full data.

6. Data Completeness and Business Buy In

Realistically you are not going to migrate 100% of the data to be found in your legacy CRM system. Do try and do so would incur a considerable cost, and probably impact the effectiveness of your new CRM by filling it with low value data. It is better to identify the key data sets early and get agreement with what you are leaving out. For example, does the new CRM require customer records that have been disabled? By reducing the scope of the data to be migrated you are increasing the chances of a successful migration.


Informatica Cloud and Windows Server 2012 R2 – CreateProcess error=14001

I just setup a new Windows 2012 R2 server with SQL Server 2012, and hooked it up to Salesforce with Informatica Cloud.

The configuration of the connections was very easy, as was the mapping of source to target fields.

However when I tried to run the mapping I got the following error:

“Internal error. The DTM process failed to start due to the following error: [CreateProcess error=14001, The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail”

Apparently this is just down to the Informatica Secure Cloud Agent not having VC++ binaries installed. This is rather confusing given there isn’t very much on this error message. Anyway I found and installed the the VC++ and all worked well.


Informatica KB on the issue:


Link to the VC++ binaries that everyone seems to insist that you don’t need:


Recover lost Powerpoint files in Office for Mac 2016

I’m finding the new Microsoft Office for Mac 2016 rather buggy, and this evening PowerPoint crashed losing all my changes throughout the day!

Powerpoint has a ‘Restore’ command under its ‘File’ menu, which was… empty.

There are lots of answers on Google to restore under this scenario for PowerPoint 2011, but nothing for the new version

So after working backwards from a solution for Excel 2016, I discovered that you just have to look in:

/Users/”User Name”/Library/Containers/

(Obviously change “User Name” with that of the user you lost the file under)

You should hopefully see some files with the word ‘_autorecover’ appended at the end. Identify one with a timestamp close to where you want to recover.

Copy that file somewhere safe (such as the desktop) and give it a .pptx suffix at the end.

Finally you can try opening with PowerPoint but it may tell you that it is corrupted. If this happens try one of the following strategies:

  • Change the suffix to .ppt and try open again with PowerPoint 2016
  • Open with Apple KeyNote instead
  • Change the suffix to .rtf and try open again in PowerPoint (this was suggested in the comments, I haven’t tried myself) and the Content Management System – Part 3

Part 1 and Part 2 of this series was about how to create a simple site in the Content Management System.

I left the Publish part for Part 3, since this can be a discrete action that a business may want to execute in isolation from the content generation.

You probably want to have a look at the simple ‘Site Configuration’ panel, which will give you some options around ‘click jacking’ (preventing the rendering of your site in an iFrame from another site), as well as allowing you to set the HTML files that will be used for the default ‘Home’ page as well as the ‘404 Not Found’ page.

‘Enable Anonymous Preview’ is useful if you want to test from a variety of devices.

Screen Shot 2015-07-21 at 11.34.15 AM

There are a few more panels which provide the ability to:

  • Custom properties : Constant values that could be reused throughout the site, such as a phone number
  • Branding properties : Expressions for use in Stylesheets
  • IP Restrictions : Restrict access to specific assets in your site based on an IP range
  • URL Redirects : Single place to manage redirects
  • User Roles : All users with access to will be listed here already
  • Languages : Add other languages variants to your site

Unfortunately my Developer edition does not allow publishing as part of the licensing, however if I had another edition then I would see a ‘Publish Changes…‘ button on the toolbar. (Note to Salesforce: Why not just have the button disabled? Hiding it makes no sense at all).

You should then be able to see your site published. The following screenshots are cheating, since I am just using Preview, but it should look exactly the same.



It is also important to note that you can duplicate and export entire sites, so it is possible to experiment as much as you like in your developer instance and later export to production when you are ready. manages basic web hosting quite effectively. It is not versatile enough for complex web hosting, and in a way it is probably a good thing because at this level of functionality it is possible to give business users a direct method to publish content.

I would still probably advise a technical administrator actually oversees the process however, since you are restricted to 25 sites, which means inevitably you will get reuse, deletions and overwriting of existing content.