Data Migration

SystemSequences in Dynamics AX help to assign different set of numbers to RECID on every table.

There are several methods of migrating data in Dynamics AX. I noticed some issues with the memory, performance and troubleshooting, as typically the memory is managed in Dynamics AX by Kernel (windows system dll).

There are migration tools which also assist in migrating, no doubt they are awesome. But the developers can still migrate data with out using any third party tools. This could be achieved by just using SSIS 2005/2008. This is a very powerful ETL tool which microsoft has already provided. The developers should only know few tricks to handle RECID's and then it is easy to pull data from any Source database and migrate to the Dynamics AX destination database. The developers need to create and sync the RECID number of a table with the NEXTVAL from SystemSequences.

Below is the sample T-SQL code to know the next RECID number for a particular table. The ID of a table can be identified from the table properties.

 Collapse | Copy Code

--The below TSQL code will help to know the next RECID number for a Particular table.

DECLARE @TableIdNumber varchar(150) 
SET @TableIdNumber = 1   -- This ID Number belongs to [Address] table and is provided as a sample.

Select Convert(Varchar(50),NextVal) NextRecID from SystemSequences where name = 'SeqNo' and tabid = @TableIdNumber   

There are few list of ID's provided in the above excel sheet. Test the above code in SQL Server.

Note : Dynamics AX 2012 only supports SQL Server 2008.

These following tables are very important since these are not specific for one module. The entire Dynamics AX depends on these tables and they are used in every module.

VendGroup table contains definitions of all Vendor Groups.

VendTable table contains vendors information for accounts payable.

CustGroup table contains definitions of all Customer Groups.

CustTable table contains the list of customers for accounts receivable and customer relationship management.

DirPartyTable contains all Address of Customers , Vendors and etc, this is also know as Global address book.

The table DirPartyTable contains all the entries in the Global address book. The Global address book stores all the persons and organizations information which are used through out the system.