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

[Blog bot]

Источник: http://alexvoy.blogspot.com/2022/05/...-files-to.html
==============

Problem

When it comes to upgrading database from AX2012 to D365FO, those 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/dyn...ent-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 files referenced in AX2012 attachments and saved externally to a on cloud SharePoint server.

Then an extension to the aforementioned class must be triggered during the standard DB upgrade procudure; 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.



<div style="background: rgb(255, 255, 255); border-color: gray; border-image: initial; border-style: solid; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">[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 = "''";

///
/// Sets a special value to File field to avoid dropping these records by standard script
/// To be run BEFORE the script
///
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();
}
}

///
/// Nulls FILE field back and updates other field to keep SharePoint links correctly
/// To be run AFTER the script
///
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/si...65FOFileShare/
monPart2, // %13 - https://monCompany.sharepoint.com/si...65FOFileShare/
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/si...65FOFileShare/
monPart2, // %13 - https://monCompany.sharepoint.com/si...65FOFileShare/
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();
}
}

///
/// Updates document reference and value records to handle file storage in the cloud.
///
[
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();
}

///
/// Updates document type records to handle file storage in the cloud.
///
[
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();
}

///
/// Executes the provided SQL statement.
///
/// The SQL statement to execute.
<span style="color: #888888;">///