Create the Migration Integration Scripts

How to Create the Migration Integration Scripts for Dynamics 365

Creation of the migration integration scripts is where the data mapping documents are put into action.  Creation of the scripts should be a straight forward process if the data mapping documents are completed in detail. This should be done in a development or test environment.

Data Sources

The data source type and format are important to consider during the creation of the migration integration scripts and can have a large impact on the speed of the scripts. To improve efficiency, tables should be indexed where possible and queries written with speed and runtime in mind. Things like formatting and transforming of data can oftentimes be done in the source query to speed up a script. For example, it is quicker to concatenate two fields in the source rather than in a transformation script or formula where the concatenation would happen on a record by record basis.

Unique Identifiers

If at all possible, define unique identifiers to all source data and store the identifiers in your target. For example, if you are bringing account records from a legacy system, a field like the account number from the legacy system should be stored in a custom field on the account entity in CRM. Unique identifiers should be a requirement for data integrations and highly encouraged for data migrations. They simplify the testing and troubleshooting processes and the ability to track the history of where your converted data originated.

Transformation Formulas

Transformation formulas are used to modify source data during the migration or integration process to deliver data to the target system in a different format than it started in the source. Examples of common transformations are concatenation, data formatting, data lookups and case statements. The data mapping document should give you a good idea what type of transformation will be required for each field.  It is also important to keep in mind speed when writing data transformation formulas. Doing lookups or case statements in formulas can slow things down. Whenever possible, do the lookups and case statements in the source query where the process will happen one time as opposed to on a record by record basis.

By following the detailed data mapping documents and keeping in mind speed and unique identifiers during the design process, the building of the migration integration scripts should go smoothly.

After you’ve completed this step, you’re ready to test the data transfer.

Are You a Data Geek?

Data gurus will love the ability to work with data within Power BI.  Learn more about Power BI here.  If you want a more formal training, consider attending a Power BI in-person training from PowerObjects.