Database upgrade scripts for D365 Finance and Operations. Development of the data migration script via temporary table.

During the data migration from Ax 2012 to D365, the table might be renamed and there might be system setups e.g. electronic signatures for the table fields. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)

In this scenario, it would be good to store the table name and id, field names, and ids of the Ax 2012 table (“AATable”) in order to apply setups to the "BBTable" in D365 during the data migration. Using a temporary SQL table is a good choice to do so. 

You can find a code that can be used as an example in order to give you an idea, below. You can copy the queries in SQL Server Management Studio to see how it works.

First of all, it is needed to create a temporary SQL table via the SQL command at the "PreSync" stage:

private str getSQLCreateTmpSQLTable()

{
    str     tmpSQLTable = “TmpSQLTable”;
    str     sqlStatement;
 
    if (! this.isTablePresent(tmpSQLTable))
    {
        sqlStatement = strFmt(@"
                CREATE TABLE [dbo].%1(
                    [KEYFIELD] [nvarchar](10) NOT NULL,
                    [FIELD1] [nvarchar](60) NOT NULL,
                    [FIELD2] [int] NOT NULL,
                    [DATAAREAID] [nvarchar](4) NOT NULL,
                    [PARTITION] [bigint] NOT NULL,
                    CONSTRAINT [I_TMPSQLTABLEDATAAREA] PRIMARY KEY CLUSTERED
                    (
                        [DATAAREAID] ASC,
                        [PARTITION] ASC,
                        [KEYFIELD] ASC
                    )
                    WITH (PAD_INDEX = OFF,
                          STATISTICS_NORECOMPUTE = OFF,
                          IGNORE_DUP_KEY = OFF,
                          ALLOW_ROW_LOCKS = ON,
                          ALLOW_PAGE_LOCKS = ON)
                    ON [PRIMARY])
                ON [PRIMARY]",
                tmpSQLTable);
    }
 
    return sqlStatement;
}

/// <summary>

/// Defines, whether the table exists in the database.
/// </summary>
/// <param name = "_tableName">
/// The name of the table.
/// </param>
/// <returns>
/// True if the table exists, otherwise false.
/// </returns>
public boolean isTablePresent(TableName _tableName)
{
   str sqlQuery = strFmt(@"IF OBJECT_ID ('[dbo].[%1]', 'U') IS NULL
                         SELECT 0
                            ELSE
                         SELECT 1",
                         _tableName);
 
   str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
 
   return str2Int(result);
}

NOTE: Enum fields are presented as number fields in Ax2012/D365. If you need to save enum field values you need to create an integer field.

Then populate the table with the data at the "PreSync" stage:

private str getSQLPopulateTmpSQLTable()

{
   TableName    tmpSQLTable = “TmpSQLTable”;
 
   str sqlStatement = strFmt(@"
            INSERT INTO [dbo].%1
                   (KEYFIELD,
                    FIELD1,
                    FIELD2,
                    DATAAREAID,
                    PARTITION)
            SELECT  [dbo].[INVENTITEMSAMPLING].[INVENTITEMSAMPLINGID],
                    [dbo].[INVENTITEMSAMPLING].[DESCRIPTION],
                    [dbo].[INVENTITEMSAMPLING].[TESTQTYSPECIFICATION],
                    [dbo].[INVENTITEMSAMPLING].[DATAAREAID],
                    [dbo].[INVENTITEMSAMPLING].[PARTITION]
            FROM [dbo].[INVENTITEMSAMPLING]
                    where [dbo].[INVENTITEMSAMPLING].[COMPLETEBLOCKING] = 1",
                            tmpSQLTable);
 
    return sqlStatement;
}

Now we can use the methods in the data upgrade method:

/// <summary>

/// Saving data to the temporary table.
/// </summary>
[UpgradeScriptDescription("@SYS113629"),
 UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),
 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),
 UpgradeScriptTable(tableStr(InventItemSampling), false, true, false, false)]
public void preSyncAQMInventItemSampling()
{
    str     sqlStatement;
       
    // Create the temporary upgrade table.
    sqlStatement = this.getSQLCreateTmpSQLTable();
    if (sqlStatement)
    {
        ReleaseUpdateDB::statementExeUpdate(sqlStatement);
    }

   

    // Populate the temporary table.
    sqlStatement = this. getSQLPopulateTmpSQLTable();
    if (sqlStatement)
    {
        ReleaseUpdateDB::statementExeUpdate(this.getSQLPopulateTmpSQLTable());
    }
}

The final step is to use the temporary table in order to manipulate data at the “PostSync” stage:

/// <summary>

/// Update the <c>InventItemSampling</c> table with the saved values.
/// </summary>
[UpgradeScriptDescription("@SYS113629"),
 UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),
 UpgradeScriptTable(tableStr(InventItemSampling), false, true, true, false)]
public void postSyncInventItemSampling()
{   
   str         sqlStatement;
   TableName   tmpSQLTable = “TmpSQLTable”;
       
   if (this.isTablePresent(tmpSQLTable))
   {
      sqlStatement = strFmt(@'UPDATE [dbo].[INVENTITEMSAMPLING]
                            SET [dbo].[INVENTITEMSAMPLING].[DESCRIPTION] = ''
                               from [dbo].[INVENTITEMSAMPLING] as iis
                            join [dbo].[%1] as upgTmp
                               ON  iis.INVENTITEMSAMPLINGID = upgTmp.KEYFIELD
                               and iis.DATAAREAID          = upgTmp.DATAAREAID
                               and iis.PARTITION           = upgTmp.PARTITION',
                             tmpSQLTable);
 
      ReleaseUpdateDB::statementExeUpdate(sqlStatement);
 
      sqlStatement = strFmt(@"DROP TABLE %1", tmpSQLTable);
 
      ReleaseUpdateDB::statementExeUpdate(sqlStatement);
   }   
}

I’ll be glad if you find this helpful.

at No comments:
Labels: , ,

Data migration to D365 Finance and Operation. Alignment of record identifiers(RecId) after the data migration.

During the data migration from Ax 2012 to D365, the table might be renamed and there might be record relations by RecId to another table. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)

In this scenario, it is necessary to develop a script in order to move Ax 2012 data as it is (including the "RecId" numbers) in order to save the data consistency. Otherwise, the migrated data can be inconsistent since record id references can point to not existing records. The script should be executed on the “PreSync” stage when both tables (“AATable” and “BBTable”) exist in the database.

When the data upgrade process is completed and you create a new record in the "BBTable" you can see the error: "Cannot create a record in "BBTable." The record already exists." But you see that there are no violations from a primary key perspective. Most probably there is a RecId sequence issue.

In order to prevent this error, it is needed to align record id numbers (RecId) after executing the data upgrade script. I would recommend performing this operation on the "PostSync" stage or via job when data migration is finished.

First of all, it is good to know the “RecId” number sequence name. In D365 Microsoft has switched from using a dedicated database table to using the standard SQL sequences functionality. So, we need to identify the SQL “RecId” sequence for the table.  The following method can be used:

/// <summary>

/// Defines the SQL sequence name of the given table.
/// </summary>
/// <param name = "_tableName">
/// The name of the table.
/// </param>
/// <returns>
/// Returns the SQL sequence name of the given table.
/// </returns>
public str getSequenceProcedureName(TableName   _tableName)
{
    str         result;
    str         ret;
    TableId     tableId;
 
    if (this.isTablePresent(_tableName))
    {
       str sqlQuery = strFmt(@"
                SELECT ID from TABLEIDTABLE
                JOIN sys.sequences 
                ON sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)
                    WHERE TABLEIDTABLE.NAME = '%1'",
                            _tableName);
 
       result  =  ReleaseUpdateDB::statementExeQuery(sqlQuery);
       if (result)
       {
           tableId = str2Int(result);
 
           if (tableId)
           {
               ret = strFmt('SEQ_%1',tableId);
           }
       }
    }
 
    return ret;
}

/// <summary>

/// Defines, whether the table exists in the database.
/// </summary>
/// <param name = "_tableName">
/// The name of the table.
/// </param>
/// <returns>
/// True if the table exists, otherwise false.
/// </returns>
public boolean isTablePresent(TableName _tableName)
{
   str sqlQuery = strFmt(@"
            IF OBJECT_ID ('[dbo].[%1]', 'U') IS NULL
                SELECT 0
            ELSE
                SELECT 1",
            _tableName);

   str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
 
   return str2Int(result);
}

When we know the sequence name, we can align the RecId number sequence with the method:

/// <summary>

/// Updates the appropriate RecId number sequence for the given table.
/// </summary>
/// <param name = "_tableName">
/// The name of the table.
/// </param>
/// <returns>
/// True if the sequence updated, otherwise false.
/// </returns>
public boolean alignNumberSequence(TableName    _tableName)
{
    boolean     ret;
    str         triggerName;
    RecId       curMaxRecId;
    RecId       newMaxRecId;
 
    if (_tableName)
    {
        triggerName = this.getSequenceProcedureName(_tableName);
        curMaxRecId = this.getMaxRecId(_tableName);
 
        if (triggerName && curMaxRecId)
        {
           newMaxRecId = curMaxRecId + 1;
           str sqlQuery = strFmt(@"ALTER SEQUENCE [dbo].[%1] 
                                    MINVALUE %2 RESTART WITH %2",
                                 triggerName,
                                 newMaxRecId);
 
           ReleaseUpdateDB::statementExeUpdate(sqlQuery);
              
           sqlQuery = strFmt(@"SELECT minimum_value 
                                from sys.sequences
                                    WHERE sys.sequences.name = '%1'",
                             triggerName);
 
           str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);
           if (result)
           {
               curMaxRecId = str2Int64(result);
               if (curMaxRecId == newMaxRecId)
               {
                   ret = true;
               }
           }
        }
    }
 
    return ret;
}

I’ll be glad if you find this helpful.

at No comments:
Labels: , ,
Subscribe to: Posts (Atom)

How to run batch tasks using the SysOperation framework

Overview As you may know, the system has batch tasks functionality . It can be used to create a chain of operations if you want to set an or...