Fixing Unbalanced TTS Error in D365FO Excel Upload

#microsoft #backend #dotnet #tutorial

When developing Excel upload in custom script functionality in Dynamics 365 Finance & Operations (D365FO) using X++, we commonly encounter the unbalanced X++ TTSBEGIN/TTSCOMMIT error. This guide shows you exactly how to fix it.

The Error You'll Encounter

Understanding How Custom Scripts Process Transactions

Lets understand how custom script is processing, when we see the class AppConsistencyCustomScriptStateChange,
when we do Run test the operation runs under ttsbegin and ttsabort, when we do Run operation, it process under ttsbegin, ttscommit.
the process is already in transaction when we upload the package

Why This Error Occurs

When you read an Excel file using classes like OfficeOpenXml.ExcelPackage or similar COM objects, these operations internally manage transactions. If your code already has an active TTS level, you'll get an unbalanced transaction error.

The Solution: Abort Transactions Before Reading Excel

The fix is simple but crucial: abort all existing transactions before reading the Excel file, then restore the TTS level afterward.

while (appl.ttsLevel() > 0)
{
    ttsAbort;
}

Since we are aborting the transaction, we need to add ttsBegin transaction begin in the end of the process to not get Unbalanced transaction errors

Complete Working Code Example

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;

class ExcelReaderForCustomScript
{

    public  CustAccount      accountNum;
    public CCMNum_BR         ccmNum ;
    /// <summary>
    /// Class entry point. The system will call this method when a designated menu
    /// is selected or when execution starts and this class is set as the startup class.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        ExcelReaderForCustomScript curentClass = new ExcelReaderForCustomScript();
        curentClass.importData();

        Info('script executed');
    }

    void importData()
    {
        System.IO.Stream                        stream;
        DialogGroup                             dlgUploadGroup;
        FileUploadBuild                         fileUploadBuild;
        FormBuildControl                        formBuildControl;
        list List = new List(types::String);

        while (appl.ttsLevel() > 0)
        {
            ttsAbort;
        }

        Dialog           dialog =    new Dialog("Upload file");

        dlgUploadGroup          = dialog.addGroup("File");
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');

        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
            CustTable   custTable;
            if(fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int rowCount, i,columncount;

                    Package.Load(stream);
                    ExcelWorksheet worksheet           = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange range     = worksheet.Cells;

                    rowCount           = (worksheet.Dimension.End.Row) - (worksheet.Dimension.Start.Row)  + 1;
                    columncount        = (worksheet.Dimension.End.Column);

                    for (i = 2; i<= rowCount; i++)
                    {
                        accountNum = (range.get_Item(i,1).Value);
                        ccmNum   = (range.get_Item(i,2).Value);
                        List.addEnd(accountNum);

                        ttsbegin;
                        update_recordset custTable setting CCMNum_BR = ccmNum
                            where custTable.AccountNum == accountNum;
                        ttscommit;

                    }
                    info(strFmt('AccountNum %1',list));
                    **ttsbegin;**
                }
            }
        }
    }

}