How to move AX2012 attachment files to SharePoint while upgrading database to D365FO

Problem

When it comes to upgrading attachments from AX2012 to D365FO, only URLs, notes, and files saved in the former's database may be transferred to the Azure Blob Storage (on-cloud). 

Therefore, all files from attachments in AX2012 should be moved to the database first in order to be successfully migrated to D365FO.

What if there are millions of them? Technically speaking it might be a good option to save them on SharePoint (on-cloud); however, unfortunately, such attachments links will be deleted, too.

You can find more detail on Document management in D365FO in https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/organization-administration/configure-document-management

Technical details

During the Ax2012-D365FO database upgrade process, among other standard classes ('scripts') ReleaseUpdateDB72_Docu is triggered, which actually deletes records in DocuValue and related tables for all files not saved in the data base including even those referenced on a local SharePoint server.

Solution

As a solution we can move all external files referenced in AX2012 attachments to a on-cloud SharePoint server first

Then an extension to the aforementioned class must be triggered during the standard DB upgrade procedure; so that it would keep existing links and update them accordingly to a new SharePoint folder structure.

For example, we can agree that existing local folders will be reproduced on the SharePoint Server instance.

The following code must be adapted accordingly to your landscape and tested first on a small set of files in a dev environment.

Please, use it at your own risk.

[ExtensionOf(classStr(ReleaseUpdateDB72_Docu))]
final class MONReleaseUpdateDB72_Docu_Extension
{
    public const str monLegacy          = 'Legacy';
    public const int monActionClassId   = 118; //DocuActionURLClassId
    public const str monName            = 'Legacy attachments for ';
    public const str monHost            = 'monCompany.sharepoint.com';
    public const str monSite            = '/sites/D365FOFileShare';

    public const str monUpgradeModifiedUser = 'MONAxDocUpgradeUser'; // fake user for marking records
    public const int monMaxRowsToUpdatePerStatement = 10000;

    public const str monPart1 = "https://monCompany.sharepoint.com/sites/D365FOFileShare/Legacy/";
    public const str monPart2 = "https://monCompany.sharepoint.com/sites/D365FOFileShare/_api/Web/GetFileByServerRelativePath(decodedurl=''/sites/D365FOFileShare/Legacy/";
    public const str monPart3 = "'')";
    public const str monPart4 = "/";
    public const str monPart5 = ".";
    public const str monPart6 = "''";

    /// <summary>
    /// Sets a special value to File field to avoid dropping these records by standard script
    /// To be run BEFORE the script
    /// </summary>
    private void monPreUpdateDocuValue_CorrectFileLocations()
    {
        SysDictTable    docuValueTable = new SysDictTable(tableNum(DocuValue));
        SysDictTable    docuRefTable = new SysDictTable(tableNum(DocuRef));
        SysDictTable    docuTypeTable = new SysDictTable(tableNum(DocuType));
        str             sqlQuery;

        Connection connection = new Connection();
        try
        {
            int impactedRows;

            // First update all DocuValues with null files and empty path
            // these files are placed in network shared folders and must be retargeted to SharePoint server
            // with setting FILE to a dummy values so that the standard next() won't delete them
            do
            {
                    sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docValue
                    SET docValue.%6 = CAST('%1' AS VARBINARY)
                    FROM %2 docValue
                    JOIN %3 docRef ON docRef.%7 = docValue.%5 
                    JOIN %11 docType ON (docRef.%12 = docType.%13 and  docRef.%14 = docType.%15)
                    AND docValue.%6 IS NULL AND docValue.%9 ='' AND docValue.%10 = 0 AND docType.%16 = 0",
                    monUpgradeModifiedUser,                                                     // %1 - upgrade modified user 
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, ModifiedBy), DbBackend::Sql),  // %4 - DocuValue.ModifiedBy
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    monMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %10 - DocuValue.Type
                    docuTypeTable.name(DbBackend::Sql),                                         // %11 - docuTypeTable
                    docuRefTable.fieldName(fieldNum(DocuRef, TypeId), DbBackend::Sql),          // %12 - DocuRef.TypeId
                    docuTypeTable.fieldName(fieldNum(DocuType, TypeId), DbBackend::Sql),        // %13 - DocuType.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef,ACTUALCOMPANYID), DbBackend::Sql),  // %14 - DocuType.ACTUALCOMPANYID
                    docuTypeTable.fieldName(fieldNum(DocuType, DATAAREAID), DbBackend::Sql),    // %15 - DocuType.DATAAREAID
                    docuTypeTable.fieldName(fieldNum(DocuType, FILEPLACE), DbBackend::Sql)      // %16 - DocuType.FILEPLACE
                    );       

                impactedRows = this.monExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == monMaxRowsToUpdatePerStatement);

        }
        finally
        {
            connection.finalize();
        }
    }

    /// <summary>
    /// Nulls FILE field back and updates other field to keep SharePoint links correctly
    /// To be run AFTER the script
    /// </summary>
    private void monPostUpdateDocuValue_CorrectFileLocations()
    {
        SysDictTable    docuValueTable = new SysDictTable(tableNum(DocuValue));
        SysDictTable    docuRefTable = new SysDictTable(tableNum(DocuRef));
        SysDictTable    docuTypeTable = new SysDictTable(tableNum(DocuType));
        str             sqlQuery;

        Connection connection = new Connection();
        try
        {
            int impactedRows;

            // First update all premarked DocuRef with the new SharePoint docuType
            do
            {
                    sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docRef
                    SET 
                    docRef.%17 = '%21' + '_' + docRef.%18
                    FROM %3  docRef
                    JOIN %2 docValue ON docRef.%7 = docValue.%5 AND docValue.%6 = CAST('%1' AS VARBINARY) and docRef.%17 <> '%21' + '_' + docRef.%18",
                    monUpgradeModifiedUser,                                                      // %1 - upgrade modified user 
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %4 - DocuValue.Type
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    monMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, StorageProviderId), DbBackend::Sql),       // %10 - DocuRef.StorageProviderId
                    docuValueTable.fieldName(fieldNum(DocuValue, AccessInformation), DbBackend::Sql),      // %11 - DocuRef.AccessInformation
                    monPart1,                                                                       // %12 - https://monCompany.sharepoint.com/sites/D365FOFileShare/
                    monPart2,                                                                       // %13 - https://monCompany.sharepoint.com/sites/D365FOFileShare/api/Web/GetFileByServerRelativePath(decodedurl='/sites/D365FOFileShare/
                    monPart3 ,                                                                      // %14 - ')
                    monPart4 ,                                                                      // %15 - /
                    monPart5 ,                                                                      // %16 - .
                    docuRefTable.fieldName(fieldNum(DocuRef, TYPEID), DbBackend::Sql),              //%17 - DocuRef.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef, ACTUALCOMPANYID), DbBackend::Sql),     //%18 - 'SPND'
                    docuValueTable.fieldName(fieldNum(DocuValue, FILENAME), DbBackend::Sql),        // %19 - DocuValue.FILENAME
                    docuValueTable.fieldName(fieldNum(DocuValue, FILETYPE), DbBackend::Sql),        // %20 - DocuValue.FILETYPE
                    monLegacy                                                                       // %21 - 'Legacy'
                    );       
                impactedRows = this.monExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == monMaxRowsToUpdatePerStatement);

            impactedRows = 0;

            // Now update all premarked DocuValues with new paths and unmark them
            do
            {
                sqlQuery =
                    strFmt(@"
                    UPDATE TOP (%8) docValue
                    SET
                    docValue.%6 = NULL,
                    docValue.%4 = 0,
                    docValue.%10 = 2,
                    docValue.%9  = '%12'+ docRef.%18 + '%15'+ docValue.%19+'%16' + docValue.%20,
                    docValue.%11 = '%13'+ + docRef.%18 + '%15'+ docValue.%19+'%16' + docValue.%20 + '%14'
                    FROM %2 docValue
                    JOIN %3 docRef ON docRef.%7 = docValue.%5 AND docValue.%6 = CAST('%1' AS VARBINARY)",
                    //@monPart1 + dr.ACTUALCOMPANYID + @monPart4 + dv.FILENAME+ @monPart5 + dv.FILETYPE
                    monUpgradeModifiedUser,                                                      // %1 - upgrade modified user
                    docuValueTable.name(DbBackend::Sql),                                        // %2 - DocuValue
                    docuRefTable.name(DbBackend::Sql),                                          // %3 - DocuRef
                    docuValueTable.fieldName(fieldNum(DocuValue, Type), DbBackend::Sql),        // %4 - DocuValue.Type
                    docuValueTable.fieldName(fieldNum(DocuValue, RecId), DbBackend::Sql),       // %5 - DocuValue.RecId
                    docuValueTable.fieldName(fieldNum(DocuValue, File), DbBackend::Sql),        // %6 - DocuValue.File
                    docuRefTable.fieldName(fieldNum(DocuRef, ValueRecId), DbBackend::Sql),      // %7 - DocuRef.ValueRecId
                    monMaxRowsToUpdatePerStatement,                                             // %8 - Max rows to update per statement
                    docuValueTable.fieldName(fieldNum(DocuValue, PATH), DbBackend::Sql),        // %9 - DocuRef.PATH
                    docuValueTable.fieldName(fieldNum(DocuValue, StorageProviderId), DbBackend::Sql),       // %10 - DocuRef.StorageProviderId
                    docuValueTable.fieldName(fieldNum(DocuValue, AccessInformation), DbBackend::Sql),      // %11 - DocuRef.AccessInformation
                    monPart1,                                                                       // %12 - https://monCompany.sharepoint.com/sites/D365FOFileShare/
                    monPart2,                                                                       // %13 - https://monCompany.sharepoint.com/sites/D365FOFileShare/api/Web/GetFileByServerRelativePath(decodedurl='/sites/D365FOFileShare/
                    monPart3 ,                                                                      // %14 - ')
                    monPart4 ,                                                                      // %15 - /
                    monPart5 ,                                                                      // %16 - .
                    docuRefTable.fieldName(fieldNum(DocuRef, TYPEID), DbBackend::Sql),              //%17 - DocuRef.TypeId
                    docuRefTable.fieldName(fieldNum(DocuRef, ACTUALCOMPANYID), DbBackend::Sql),     //%18 - 'SPND'
                    docuValueTable.fieldName(fieldNum(DocuValue, FILENAME), DbBackend::Sql),        // %19 - DocuValue.FILENAME
                    docuValueTable.fieldName(fieldNum(DocuValue, FILETYPE), DbBackend::Sql),        // %20 - DocuValue.FILETYPE
                    monLegacy                                                                       // %21 - 'Legacy'
                    );
                impactedRows = this.monExecuteSQL(sqlQuery, connection);
            }
            while (impactedRows == monMaxRowsToUpdatePerStatement);

        }
        finally
        {
            connection.finalize();
        }
    }

    /// <summary>
    /// Updates document reference and value records to handle file storage in the cloud.
    /// </summary>
    [
        UpgradeScriptDescription("Updates document value records to handle file storage in the cloud"),
        UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
        UpgradeScriptType(ReleaseUpdateScriptType::PartitionScript),
        UpgradeScriptTable(tableStr(DocuRef), false, true, true, false),
        UpgradeScriptTable(tableStr(DocuValue), false, true, true, true)
    ]
    public void updateDocuValue_CorrectFileLocations()
    {
        this.monPreUpdateDocuValue_CorrectFileLocations();
        next updateDocuValue_CorrectFileLocations();
        this.monPostUpdateDocuValue_CorrectFileLocations();
    }

    /// <summary>
    /// Updates document type records to handle file storage in the cloud.
    /// </summary>
    [
        UpgradeScriptDescription("Updates document type records to handle file storage in the cloud"),
        UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),
        UpgradeScriptType(ReleaseUpdateScriptType::PartitionScript),
        UpgradeDependsOnTaskAttribute(methodStr(ReleaseUpdateDB72_Docu, updateDocuValue_CorrectFileLocations)),
        UpgradeScriptTable(tableStr(DocuType), false, true, true, false)
    ]
    public void updateDocuType_CorrectFilePlacement()
    {
        next updateDocuType_CorrectFilePlacement();
        this.monCreateNewDocuType();
    }

    /// <summary>
    /// Executes the provided SQL statement.
    /// </summary>
    /// <param name="_sqlStatement">The SQL statement to execute.</param>
    /// <param name="_connection>The SQL connection to use; otherwise a new connection will be created.</param>
    /// <returns>The number of rows impacted by the statement.</returns>
    private int monExecuteSQL(str _sqlStatement, Connection _connection = null)
    {
        Connection sessionConn = _connection ? _connection : new Connection();
        try
        {
            Statement statement     = sessionConn.createStatement();
            new SqlStatementExecutePermission(_sqlStatement).assert();
            int impactedRows = statement.executeUpdate(_sqlStatement);
            statement.close();
            CodeAccessPermission::revertAssert();
            return impactedRows;
        }
        finally
        {
            if (!_connection)
            {
                sessionConn.finalize();
            }
        }
    }

    /// <summary>
    /// gets a Set of all legal entities present in the staging
    /// </summary>
    /// <returns>Set</returns>
    public Set getCompanySet()
    {
        DocuRef docuRef;
        Set companySet = new Set(Types::String);
        while select ActualCompanyId from docuRef
            group by ActualCompanyId
        {
            companySet.add(docuRef.ActualCompanyId);
        }
        return companySet;
    }

    /// <summary>              
    /// Creates new DocuType records for legacy attachment moved now to SharePoint 
    /// </summary>
    private void monCreateNewDocuType()
    {
        Set             companySet  = this.getCompanySet();
        SetEnumerator   se          = companySet.getEnumerator();
        DocuType        documentType;
        ttsbegin;
        while (se.MoveNext())
        {
            SelectableDataArea currCompany = se.current();
            changecompany(currCompany)
            {
                DocuTypeId typeId = monLegacy+'_'+currCompany;
                if(!DocuType::exist( typeId))
                {
                    documentType.clear();
                    documentType.TypeGroup                  = DocuTypeGroup::File;
                    documentType.RemoveOption               = DocuRemoveOption::DocumentAndFile;
                    documentType.FileRemovalConfirmation    = NoYes::Yes;
                    documentType.TypeId                     = typeId;
                    documentType.ActionClassId              = monActionClassId; //DocuActionURLClassId
                    documentType.Name                       = monName+currCompany;
                    documentType.FilePlace                  = DocuFilePlace::SharePoint;
                    documentType.Host                       = monHost;
                    documentType.Site                       = monSite;
                    documentType.FolderPath                 = monLegacy+'/'+currCompany;
                    documentType.doInsert();
                }
            }
        }                     
        ttscommit;
    }

}