A Simple example of migrating data using AX 2012 Data Migration Framework

This topic explains in detail and illustrates a simple example of migration of data using the Ax 2012 Data migration framework.

Before you begin, your environment must include the following components:

1. A running version of Microsoft Dynamics AX 2012, along with Ax 2012 Data migration framework installed and running.
2. Access to demo files which are copied to Program files during installation of Ax 2012 Data migration framework. (typically under path: "<root>/Program Files/Microsoft Dynamics AX 2012 Data Migration Framework Client Components (Beta)/DemoFiles")

After the successful installation of Ax2012 DMF, there will be a new module 'Data migration framework' in the Ax module list with Area page as shown below.

For an End-to-End scenario of Data Migration, the flow would be some thing like:

1. Setup of Data migration framework
• Data migration framework parameters (form) --> to provide share folder path
• Source data fomats (form) --> to provide details about source data format
2. To Identify the target entities.
• Target entities (form)
• create a custom entity for migration (form)
3. Source to staging
• Processing group (form)
4. Staging to Target
• Processing group > Execution history (form)
5. Staging cleanup
• Staging cleanup (form)

Step #1: Setup of Data migration framework:

The DMF requires a shared directory that the service account must have read and write access to. This can be provided by:

1. Open Data Migration Framework > Setup > Data migration framework parameters (form)
2. Enter a share in 'Location of shared directory' and then click Validate.
   If the DMF service account has write access to the share, the validation icon turns green.

And now, we need to define the data format from the external source. External sources can be files, a different database or a different version of Microsoft Dynamics Ax. But for Beta, only the source type supported is file.  In future, there might be support for other source formats as well.

For files, you must define whether the data is coming from a list that is delimited (comma-separated (CSV)) or is fixed width.

1. Open Data Migration Framework > Setup > Source data formats (form)
2. Click New and provide a name and description for the source.
3. Verify that the source type is File.
4. Set the following general settings for files.
• File format => DelimitedSelect Delimited or Fixed width. Delimited formats use a specific character to separate fields. Fixed width formats set aside a specific number of characters for each field.
  Note: The options for Delimited and Fixed width are the same. Field widths are defined for each entity, not in the data format.
• First row header => trueSelect this option if the first row of your data files contains header information.
  For fixed width formats, you can specify delimiter characters to define the values in the header row, if there is a header row.
• Row delimiter => {CR}-{LF}Select the delimiter for rows.
  {CR}{LF} if The header row is delimited by a carriage return/line feed combination
  {CR} if The header row is delimited by a carriage return.
  {LF} if The header row is delimited by a line feed.
  Semicolon {;} if The header row is delimited by a semicolon.
  Colon {:} if The header row is delimited by a colon.
  Comma {,} if The header row is delimited by a comma.
  Tab {t} if The header row is delimited by a tab.
  Vertical bar {|} if The header row is delimited by a vertical bar.
• Column delimiter => Comma {,}
  Select the delimiter for columns. See the description of the Row delimiter parameter for the available values.
• Text qualifier => "Is used in case your file contains a column of type string, whereas, the value of that column might contain {,}  column delimiter. For example, if your organization names include commas, but the value that you are using for a column delimiter is a comma, then you should enter another value to delimit text values. In this example, you might want to use a quotation mark ".
  So here, we need tEnter a value to use to delimit text values in a row that may conflict with the value you are using for a row or column delimiter.
5. Set the following general parameters for regional settings.
• Code page => 1252
  Specify the code page for non-Unicode text.
• Unicode => false
  Select this option to indicate whether to use Unicode.
• Language locale => en-us
  Specify the locale to provide language-specific information for ordering, and for date and time formats.
6. Set the following general parameters for multiple value separators.


• Multiple value separator => ;Optional: Set a value to delimit fields that contain multiple values that are associated with a single record, such as email addresses, phone numbers, or URLs.
7. Click Application to set the parameter values to Dimensions and Name sequence.
8. Set the following general parameters for dimensions.
• Dimension code => CostCenter; Department; PurposeAs you know inside Ax 2012, Dimension is represented by a 64 bit Integer, however for an end user it is still a combination of string values. So we need to select the format in which user needs to send the Dimensions.
  This is Optional: Specify the list of financial dimensions that are part of the source file. The codes for the financial dimensions are pulled from Microsoft Dynamics AX.
• Chart of accounts delimiter => -
  Delimiter seperating the individual dimensions. The delimiter between financial dimension values.
• Dimension format => CostCenter-Department-Purpose
  This is a predefined format of the dimension, based on the financial dimensions and delimiter that you selected. This field is automatically populated.
9. Set the following value for name sequences

   
   

   Name sequence => (left blank for demo purpose)Specify the default name sequence for parties that are created during migration. Create a data format for each data structure that you are migrating.
   

Step #2: Is to Identify the entities to migrate to Ax2012

For this we need to go to Target entities (form). This shows the list of entities shiped with Ax2012 DMF. We can also create a custom entity.


 Step #3: Creating Processing group Processing group is similar to definition groups in standard Ax while Import/Export of data. Processing group represent a list of entities that should be migrated together. So to proceed further we need to create a Processing group and add entities to it as shown below: 

1. Open Processing group (form) from Data migration framework > Common > Processing group
   Create a new record with
• Group name => AR
• Description => Accounts receivable
  Save record to enable "Entities" button.
   
2. The next step is to add entities to the newly created processing group. Click button "Entities" to open up a new form 'Select entities for processing group' and now 


• Add Entity name => Customer
  can choose from the list of entities shown in the look up based on requirement.
• Add Source data format => CSV
  this indicates the data format in which data is provided from Source. In our case, we will choose CSV which we create above.
• Add Sample file path => "<root>/Program Files/Microsoft Dynamics AX 2012 Data Migration Framework Client Components (Beta)/DemoFiles/Delimited/CustomerEntity_ContactInfo"
  Sample file would contain the coloumns which are part of customer file and may contain some data.  
3. The next step is to map the above file to the staging table. To do this, we need to click the button "Generate source mapping". This will read the column names from the file and automatically map them with the columns in the Staging table.
   An Infolog will pop up with a most likely message 'Mapping has complete successfully'. If you get any errors, you will have to fix them before proceeding further.
4. If you want to modify the mapping or verify the Source mappings. You can click the button "Modify Source mapping". It will open up a new form.
   Inside 'Map source to staging' form, there are two Views,


 
• Mapping visualizationThis is the graphical representation of Source to Staging table mapping. Here on the left side, Source block represents the data from file. And on the right side, Staging block represents the staging table inside Microsoft dynamics Ax2012. And mappings are shown as a line drawn from Source to staging.
• Mapping details
  Whereas Mapping details is the X++ grid equalent to the visualization. Again on left side, Source field represents the data from the file. And on right side, Staging field representing columns from the staging table.
5. Assuming the mapping is correct, and now we can actually preview how the data will be entered in the Staging table. For this, we can click the button "Preview source file". This will show the preview (without actually moving data from file to staging) in the preview pane in 'Select entities for processing group'form. Here we can verify if the data is appearing as expected.
6. After verifying the mapping and preview, close the form 'Select entities for processing group'

Step #4: Getting the data into staging table

To get data from file to staging table,

1. On the 'Processing group' form, click button "Get staging data"|
   This will pop up a Microsoft Dynamics Ax Dialog box, asking for a job id which is basically to schedule a job to get data for that particular processing group. Enter any meaningful values as shown in the picture below and click Ok.
2. A new form 'Staging data execution' is shown with the details like, Entity name, File path. Here, the option of changing the file path is provided. And in case you change the file path, the option to preview the data in the stagging table is also avaliable. This is achieved by clicking the button "Preview".
3. Once everything is verified and you are ready to start migrating data from file to staging table, Click button "Run"
   You will be shown a dialog, providing you the option to select whether the processing should be done using batch or non-batch. Let's consider non-batch for the demo purpose. So click "Ok"button.
4. If there are no errors and the staging records have been updated successfully. You will recieve an infolog with details about the records inserted or updated.
5. Once this is done, the other buttons on the processing group will be enabled automatically. For example, by clicking the button "Execution history". We can see all the details related to a particular Job ID.

Step #5: Verify Staging data and Copy data to Target (Ax2012)

Observe the above screenshot, in the 'Execution history (form)' there are three buttons enabled, View staging data, Copy data to target and Log respectively. These play the role of verifying staging data and copying data from staging to Target.

1. In the Execution history form, click button "View staging data" to open up a new Dynamic form with the Entity name as the title. The form lists out all the 6 records entered into staging table.
2. Now the user can validate data in the staging table. And to make sure the data is clean, i.e, to make sure that all the references are correct, we can always click "Validate" button - This is validate the current record or the selected records.
   If you want to validate all the records at the same time, press button "Validate all". This will verify whether all the references for the staging data in the target are correct, if they are not, the system will warn you regarding them with details.
   And in case of warning, you need to correct the references to proceed further.
3. For the demo, I will click button "Validate all" and should get an infolog message saying validation is successful.
4. Close the form 'DMFCustomerEntity: Customers'
5. Next, we need to copy the data from Staging to Target. For this we have a button "Copy data to target". Click it.
   A new dialog form opens up with options to select Job ID, Run for/Criteria. For the demo purpose and also as we need to migrate all the 6 records from Staging to Target, we will choose Run for => All.
6. After click "Ok", to open up a 'Target data execution' form which will show the details of the entity update for Target execution.
7. Now click button "Run" to pop up a dialog form enabling us to choose for either Batch processing or non-batch processing. For the demo, let's consider non-batch.
   Just click "Ok" button.
8. If processing runs without any error, an infolog with number of rows created/updated will pop out.
    

Step #6: Verify data copied to Target

1. To verify Target data, in the 'Execution history' form, click button "View staging data" to open a form which lists the total records targeted for migration. 
2. Select any desired record and click button "Target", this will open the actual Customer form with the selected record. This way we can actually validate whether the data from Staging table is properly inserted into the Target tables.
3. Now after validation, we can say that the Data from File is successfully migrated to Target (Ax2012) using intermediate staging table.

Step #7: Final step is Staging cleanup

Staging cleanup will delete the data from staging table once the data is been migrated to the target. To do this follow below steps.

1. Go to Data migration Framework > Periodic > Staging cleanup, this would open up a dialog with options to provide Entity, Processing group and Job ID.
   We will have to provide the values used for Data migration
   
2. After entering valid values, click button "Ok". This is will actually clean the staging table and an infolog pops up.
   

This ends the demo for migration of data from a File (source) to Dynamics Ax 2012 (target) using intermediate staging tables.

Internal Links:
Simplified briefing about Ax 2012 DMF
Step by Step DMF Installation process
Simple example of DMF usage
For Troubleshooting DMF installation issues
Known Issue with DMF #1
Known Issue with DMF #2
Best practises for DMF usage

Youtube link for a very nice related video:
http://www.youtube.com/watch?v=vbMrqE7sZAs
 

Best Practises for use of Data Migration Framework

Now that we have installed, troubleshooted and verified that installation of AX 2012 Data Migration Framework is completed successfully, lets move towards One Important best practise suggested by Microsoft.

Microsoft states in DMFs user guide that: “Because the staging environment is highly normalized and may require significant processing bandwidth, we recommend that you increase the Maximum buffer size setting for your environment while you migrate data. Use the Server configuration utility to set the value.”
If you want to take this advice of Microsoft, the below steps show how to do that.

1. Launch the server configuration utility (Start menu > Administrative Tools > Microsoft Dynamics AX 2012 Server Configuration),
2. Click Manage > Create configuration...
   And in the opened dailog, fill Configuration name with any desired string as shown:
   
   And click ok.
3. Now in the new configuraiton, Go to the database tuning tab, and change the buffer size from 48, to say 54 or another apt value based on amount of data to be migrated.
4. You will getting a warning message by doing so. Since we are doing this on purpose, we can ignore this warning. However it’s valid to point out that after your done with the data migration framework, it’s best to change this setting back to default.
   
5. After doing this, and clicking apply and click Yes on the dialog which pops up. It will restart the Microsoft Dynamics AX 2012 AOS instance.

And you are ready to continue with your work now.

Internal Links:
Simplified briefing about Ax 2012 DMF
Step by Step DMF Installation process
Simple example of DMF usage
For Troubleshooting DMF installation issues
Known Issue with DMF #1
Known Issue with DMF #2
Best practises for DMF usage
 

Troubleshooting issues during an installation of the Data Migration Framework

#Issue 1: Error with Windows Installer package
Setup will not install the Data Migration Framework service on a 64-bit computer without Microsoft Visual Studio 2010 installed and running.
The installation fails with the error "There is a problem with this Windows Installer package. A program required for this installation to complete could not be run. Contact your support personnel or package vendor."

ResolutionInstall the Microsoft Windows SDK for Windows 7 and .NET Framework 4 from http://www.microsoft.com/en-us/download/details.aspx?id=8279.

On the Installation Options page, select the following options, and then complete the installation.

• Under .NET Development, select Tools.
• Under Redistributable Packages, select Microsoft Visual C++ 2010.
• Open a command prompt with Run as administrator rights.
• Depending on which version of SQL Server is in your environment, run one of the following commands.
• For SQL Server 2012, run Command
  <System Drive>\Program Files\Microsoft SDKs\Windows\v7.1\bin\NETFX 4.0 Tools\gacutil.exe" -i "C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Service (Beta)\Microsoft.Dynamics.AX.DMF.DMFErrorDescription.dll"
• For SQL Server 2008, run Command
  <System Drive>\Program Files\Microsoft SDKs\Windows\v7.1\bin\gacutil.exe" -i "C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Service (Beta)\Microsoft.Dynamics.AX.DMF.DMFErrorDescription.dll"
• Start the Microsoft Dynamics AX 2012 Data Migration Framework service.

#Issues 2: Data Migration Framework will not compile

If after installing the Data Migration Framework, you cannot compile,

• validate that the Data Migration Framework has installed correctly.
• Verify that the Microsoft Dynamics AX Data Migration Framework Service is running.
• Validate that the Data Migration Framework DLLs have been placed in the appropriate folders. The Data Migration Framework DLLs include:
• Client DLLs
  Microsoft.Dynamics.AX.DMF.Mapper.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin
  Microsoft.Dynamics.AX.DMF.PreviewGrid.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin
  Microsoft.Dynamics.AX.DMF.ServiceProxy.dll – should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin
  Microsoft.Dynamics.AX.DMF.DriverHelper.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin
  Microsoft.Dynamics.AX.DMF.SSISHelper.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin
• Server DLL

• Microsoft.Dynamics.AX.DMF.SSISHelper.dll — should be present in the folder C:\Program Files\Microsoft Dynamics AX\60\Server\<AosInstance>\bin
  Microsoft.Dynamics.AX.DMF.ServiceProxy.dll – should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\Server\<AosInstance>\Bin
  Microsoft.Dynamics.AX.DMF.DriverHelper.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\ Server\<AosInstance>\Bin
  Microsoft.Dynamics.AX.DMF. DMFErrorDescription.dll — should be present in the folder C:\Program Files (x86)\Microsoft Dynamics AX\60\ Server\<AosInstance>\Bin

Resolution
The following is how to proceed if the DLLs are not present for each installation type.

1. For Installation type : Client
   Copy the DLLs from the installation location (C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Client Components(Beta)) to the folder indicated.
2. For Installation type : Server
Copy the Microsoft.Dynamics.AX.DMF.SSISHelper.dll from the installation location (C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Server Components(Beta)\ ) to the folder indicated.

Use the Assembly Registration Tool (Regasm.exe) to register the DLL. At a command line, run the following two commands:

1. regasm Microsoft.Dynamics.AX.DMF.SSISHelper.dll /tlb: Microsoft.Dynamics.AX.DMF.SSISHelper.tlb
2. regasm Microsoft.Dynamics.AX.DMF.ServiceProxy.dll /tlb: Microsoft.Dynamics.AX.DMF.ServiceProxy.tlb

Issue 3: Data Migration Framework service location changes
If you need to update the location where you are running Integration Services and the Data Migration Framework service, you can update the configuration information with correct computer name in the following files:
http://<<DMF Service ComputerName>>:7000/DMFService/DMFServiceHelper.svc

This file can be found in the following locations:

1. For Client, C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\ Microsoft.Dynamics.AX.DMF.ServiceProxy.dll.config
2. For Server, C:\Program Files\Microsoft Dynamics AX\60\ServerAosInstance\Bin\ Microsoft.Dynamics.AX.DMF.ServiceProxy.dll.config
3. For Service, C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Service (Beta)\ Microsoft.Dynamics.AX.DMF.SSISHelperService.exe.config

Internal Links:Simplified briefing about Ax 2012 DMF
Step by Step DMF Installation process
Simple example of DMF usage
For Troubleshooting DMF installation issues
Known Issue with DMF #1
Known Issue with DMF #2
Best practises for DMF usage

Step by Step installation procedure of Ax2012 Data Migration Framework (Beta 2)

This topic describes how to install the Data Migration Framework for Microsoft Dynamics AX 2012, Beta 2 version.

Before you begin, your environment must include the following components:

1. A running version of Microsoft Dynamics AX 2012 that has been configured for your business.
2. A running version of Microsoft SQL Server Integration Services that is running the same version of SQL Server that is hosting the Microsoft Dynamics AX business and model store database.

Important: Because the staging environment is highly normalized and may require significant processing bandwidth, we recommend that you increase the Maximum buffer size setting for your environment while you migrate data. Use the Server configuration utility to set the value.

Data Migration Framework components should be installed on: (You must run the installer locally on each computer)

1. computers running Integration Services,
2. on a computer running an Application Object Server (AOS) instance,
3. and on a computer running the Microsoft Dynamics AX client.

You can Download the installation package for the Data Migration Framework, DataMigrationFrameworkSetup.zip, and extract it to a local folder

(from InformationSource which requires valid Partner source ID)

#1: Install the Data Migration Framework on Integration Services

On the computer running Integration Services, right-click DmfSetup.exe, and click Run as administrator.

Important: If you do not run Setup as an administrator, the Data Migration Framework DLLs will not be copied to the correct locations, nor registered.

In the Setup wizard, accept the license terms, and on the Select components to install page, click Data Migration Framework service.

The Prerequisite validation screen appears. If the computer passes the validations required, click Next. (And If the computer does not pass the validation, install the prerequisites, and then re-launch Setup)

On the Specify a service account page, enter the same account used for the AOS service, and then click Next.

On the Select version of Microsoft SQL Server page, select the appropriate version, and click Next.

On the Ready to install page, click Install.

On the Installation completed page, select Show logs to display the log files, and then click Finish. (The log file is stored in the same location that Setup was run from)


It is always good to check in the Log files that no errors occurred.

#2: Install the Data Migration Framework on an AOS instance

On a computer that is running an AOS instance, right-click DmfSetup.exe, and click Run as administrator.

In the Setup wizard, accept the license terms, and on the Select components to install page, click Application Object Server (AOS) component.

The Prerequisite validation screen appears. If the computer passes the validations required, click Next.

On the Select an AOS instance page, select an AOS instance, and then click Next.

On the Specify SQL Server Integration Services location page, select the computer running Integration Services, on which you installed the Data Migration Framework service, and click Next.

On the Ready to install page, click Install.

On the Installation completed page, select Show logs to display the log files, and then click Finish.


#3: Install the Data Migration Framework on a client

On a computer that is running a Microsoft Dynamics AX client, right-click DmfSetup.exe, and click Run as administrator.

In the Setup wizard, accept the license terms, and on the Select components to install page, click Client component.

The Prerequisite validation screen appears. If the computer passes the validations required, click Next.

On the Ready to install page, click Install.

On the Installation completed page, select Show logs to display the log files, and then click Finish.


#4: Install the Data Migration Framework model

After all of the components have been installed, you must install the Data Migration Framework model. This should be done on the client computer. Import the DataMigrationFramework.axmodel file from the location to which you installed the Data Migration Framework.

Note: Verify that the Microsoft Dynamics AX Management Shell is pointing to the database that you want to install the model in.

Drain client connections to the AOS instance that you are working with.

Stop the AOS.

Use one of the following command-line tools to import the model.

Windows PowerShell command:
Install-AXModel -File "C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Client Components(Beta)\DataMigrationFramework.axmodel"

From Command prompt - AXUtil:
axutil import /file:"C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework Client Components(Beta)\DataMigrationFramework.axmodel"


Restart the AOS service.

Start the client.

In the Model store has been modified dialog box, click Compile and synchronize.

When the synchronization is completed, click Compile into .Net Framework CIL.

Note: If the dialog box does not open by itself, follow these steps.
Compile the application from System administration > Periodic > Compile.
Click System administration > Periodic > Database > SQL administration. On the Table actions menu, click Synchronize database.
Compile into .NET CIL from System administration > Periodic > Compile.

After the model has been compiled into .NET CIL, the Data Migration Framework button is added to the navigation pane. This means that Installation is successful.

Internal Links:
Simplified briefing about Ax 2012 DMF
Step by Step DMF Installation process
Simple example of DMF usage
For Troubleshooting DMF installation issues
Known Issue with DMF #1
Known Issue with DMF #2 
Best practises for DMF usage  

AX2012 Data Migration Framework

What is AX2012 Data Migration Framework?
It is an extension to AX 2012 that provides a way to migrate data from external sources to AX 2012. Data that you can migrate include master data, open stock and balances.

Where can you download it from?
The Data Migration Framework is available from the InformationSource services download page. (you will need an active Partner source ID to download this Beta 2 version)

What are the advantages?
The Beta 2 version of the DMF includes support for the following new features:

• ODBC connections as a data source.
• Microsoft Dynamics AX databases as a data source. You can now export and import Microsoft Dynamics AX, and copy company information by using Data Migration Framework entities.
• Direct table and composite entity types.
• A direct table entity enables you to migrate data from a source to a target Microsoft Dynamics AX table directly, without going through a staging table or applying any business logic.
  The direct table entity must be a one to one mapping from source to target.
• A composite entity groups multiple related entities together. An example of a composite entity is a Sales Order and Sales Line entities combined together.
  The composite entities are supported only for file data sources.
• Parallel execution of threads to move data from staging to target tables.
• Using the DMF as a service. The framework can now be executed by external applications.
• Error-handling enhancements. The DMF provides more detailed logs, and can skip rows that contain errors.
• Number sequence support.
• Remote installations of Microsoft SQL Server Integration Services. It is no longer necessary to install Integration Services on the same computer as the Application Object Server (AOS).

Data Migration Framework (DMF) Architecture:

Configuration steps are required before you can migrate data to MS Dynamics Ax:

 

DMF Processing – after successful configuration:

Below are the processes that run when data is copied from source to staging, and from staging to target.

Internal Links:
Simplified briefing about Ax 2012 DMF
Step by Step DMF Installation process
Simple example of DMF usage
For Troubleshooting DMF installation issues
Known Issue with DMF #1
Known Issue with DMF #2
Best practises for DMF usage

More information available at:
http://technet.microsoft.com/en-us/library/jj225591.aspx
http://kaya-canada.com/data-migration-framework-in-ax2012