img1

This is the second and final part of the two blog series on Test Data Transfer Tool for Microsoft Dynamics AX 2012. In this blog post I am going to discuss more about how to use the tool. But before using the tool you have to generate the meta data from the source AOS. Below you find the steps involved in generating the meta data from AX:

How to export the meta data from the AX?

  1. Open the development workspace in the source AX client. Open the XPO import form.
  2. Browse to the Test Data Transfer Tool installation directory. Typically you can find it at:

C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)

3. There you will find the XPO named “MetadataXMLGenerator.xpo”. Import it on the AOS from where the data is going to be exported.

4. Once the XPO import is completed then go the Jobs node in the AOT. From there run the job named “MetaDataXMLGenerator”. It will take some time to get executed and up on the completion will generate the file named “Metadata.xml” in your temp folder. The typical path is:

C:\users\<Windows user name>\Appdata\Local\temp\1\.

The path will also be shown in the InfoLog message on the job completion.

5. Copy the generated filed from the temp folder and paste it to the [Lists] folder under the tool installation folder in the Program Files. The typical path is:

C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]

If the file already exists then override the existing file.

Please make sure to keep the meta data file generated periodically in order to keep file always current. This file is used while exporting the data from the source system.

As a second step you are required to mention the tables that you want to export.

How to export only the selected tables?

By default the tool is configured to export all the AX database tables. If you want to export only selected tables then you have to specify the exclude list. Following are the steps to exclude tables from the export list:

  1. Add a text file in the [Lists] directory. The file name must begin with Exclude – and the file name extension must be .txt. You will already find number of exclude files under the [Lists] folder as part of the default tool installation. The typical path to the [Lists] folder is:

C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]

2. Specify the name of the table or set of tables you want to exclude. That is you don’t want to export them. The exclude files expect a regular expression. For example to exclude a single table InventTable write the following  regular expression in the text file:

//Will only exclude the single table named InventTable

InventTable

To exclude all the tables that begins with Invent use the following regular expression:

//This will exclude all the tables that begins with Invent

Invent.*

If you want to export only the CustTable and VendTable then use the following regular expression:

.*(?<!^CustTable) (?<!^VendTable)

The above expression will make sure to exclude all the tables from export except CustTable and VendTable. If you need one more table to be exported say InventTable then simply append the following expression to the above:

(?<!^InventTable)

Your expression will now become:

.*(?<!^CustTable) (?<!^VendTable) (?<!^InventTable)

Add the same expression for as many tables as you want to export. The table names should match the SQL Server table names. The table names are case in-sensitive.

How to filter the records in the exported data set?

It is also possible to filter the records that are going to be exported. For example you only want to export the data for a specific AX company. This can be achieved through the Filters.xml file. Unlike table exclude files, there can be only one Filter.xml.

For example you want to export all the Vendors from the ceu company only.

  1. Opens the text editor of your choice and save a file “Filters.xml” under the [Lists] folder and continue editing the file.
  2. Add a root element  node “<tables>” to the Filters.xml as shown below:

img1

3. Add a node named “<table>” as a child node to the root element. Specify the SQL Server table name to which you want to apply the filter for in the attribute “name” as shown below:

img2

Add as many table nodes you want to add for which you want to apply the filter for. For each table there can be only table node in the Filters.xml file.

4. In order to define a filter for a table add a child node named “<filter>” under the “<table>” node of your choice as show below:

img3

 

5. Now it is time to specify the actual filter clause which is in your case is that to export all the vendor records from the VendTable for the CEU company. The SQL for the required filter is:

 

Select *

From VendTable

Where DataAreaId = ‘ceu’

 

The tool also understands the similar syntax as shown above with the exception the field that has to be filtered must be wrapped in the XML node “<field>” as shown below:

img4

Please note that you can’t filter the records using the other tables fields.

How to filter the records using multiple conditions?

 It is also possible to write a more complex filter using multiple conditions. For example you want to export the vendor records that belong to the CEU company and whose vendor group is 10. The typical SQL query to satisfy the requirements is:

Select *

From VendTable

Where DataAreaId = ‘ceu’

And VendGroup = ’10’

 

This is how you can achieve the same using the Filters,xml file:

img5

How to filter the records using the SQL sub-query?

 At times you are required to filter the records based on the existence or non-existence in other tables. For example you have been asked to export only those parties from the DirPartyTable who are vendor. Typical SQL to achieve this is:

Select *

From DIRPARTYTABLE

Where RECID In

(

Select PARTY

From VENDTABLE

Where DATAAREAID = ‘ceu’

)

This can be achieved using the Filters.xml as follows:

img6

The thing which you have to remember while using the sub-query is to use the fully qualified table name(s) in your sub-query. The full qualified SQL Server table name can be specified as:

<Database name>.dbo.<Table name>

In my case it is AX2012FP.dbo.VendTable

Required security permissions

 This tool requires SQL Select permissions on the tables you want to export. Please note that AX security rights didn’t work for this tool.

How to export the data?

Before running the tool make sure that you have the SQL client tools installed and yours Windows user can connect to the SQL Server instance. Please follow the below mentioned steps to export the data:

  1. Open the command prompt. Set the current directory to:

C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)

2. Type the command:

DP.exe EXPORT        <<Directory>>   <<Database name>>      <<Server>>

Where:

i.            Directory is the folder where you want to keep the exported files.

ii.            Database name is the AX database name.

iii.            Server is the SQL server instance name.

The export process will generate three files for each table. The .out file contains the actual data.

How to import the data?

The import process is similar to the export with the exception that the tool will import all the data found in the import directory. The tool will not consider the data filters at the time of import.

DP.exe IMPORT                <<Directory>>   <<Database name>>      <<Server>>

Leave a Reply

Recent Comments

    Archives

    Categories