Thursday, March 15, 2018

Test Data Transfer tool

1. Download Test Data Transfer Tool
2. Install it on Database server
3. Import MetadataGenerator.XPO in Source AX application. (From where you want to export data)
4. Run job MetadataGenerator and find the  Metadata file
5. Paste Metadata file into [Lists] folder. ( check your installation path in DB server)  it will be like "C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)\[Lists]"
6. To export data from specific company, open SQL Sever management studio and select your source database. Right click and new query. Copy paste below given code into query window. Replace your company name. (Company Name highlighted in bold font). Run the script and copy ouput.
7. Create new text file and paste query output. Save this file with name Filters.xml
8. Put Filters.xml file into [Lists] folder (Path given in step 5)
9. Open command prompt with Run as administrator.
10. Brows the path of DP.exe ("C:\Program Files (x86)\Microsoft Dynamics AX 2012 Test Data Transfer Tool (Beta)")
11.  Enter below command and hit enter.
DP.exe  Export "D:\Exported Data"  MicrosoftDynamicsAX  AXDBServerName

Filters.xml file content is as below.

-- Declare the variables to store the values returned by FETCH.
DECLARE @tableName varchar(100);
DECLARE @fieldName varchar(100);
SET @fieldName = 'DataAreaID';

DECLARE table_cursor CURSOR FOR
SELECT  Name FROM sys.tables ORDER BY Name;

OPEN table_cursor;

-- Perform the first fetch and store the values in variables. 
PRINT '<tables>'
FETCH NEXT FROM table_cursor
INTO @tableName;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- All tables don't have DataAreaId field, So check first that table is having dataareaid filed and then
-- add tableName to xml
IF COL_LENGTH(@tableName, @fieldName) IS NOT NULL
BEGIN
-- Concatenate and display the current values in the variables.
PRINT '<table name="' + @tableName +'"><filter><field>DataAreaID</field>=''USMF''</filter></table>'-- + @tableName --+ ' ' +  @LastName
END
-- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM table_cursor
   INTO @tableName;
END
CLOSE table_cursor;
PRINT '</tables>'
DEALLOCATE table_cursor;
GO