BACPAC import failure fix

It is a common task to move databases between a Microsoft-managed environment and a self-service machine. From Azure SQL we can only take a BACPAC export, which needs to be imported/converted into Microsoft SQL Server format. With the recent changes and improvements of security, you may be facing an error message when trying to move the database backups. Let’s have a look at the BACPAC import and export failure fix to address the following error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

The tool to process the BACPAC file is called SqlPackage. You should install the latest DAC version first for to have the benefit of fixed and performance improvements.

Example of a BACPAC export from your MSSQL DB:

.\SqlPackage.exe /Action:export /ssn:SourceServerName /sdn:AXDB_source /tf:F:\Backup\AXDB.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /SourceTrustServerCertificate:true

Example of a BACPAC import into your MSSQL DB:

.\SqlPackage.exe /Action:Import /sf:C:\DynamicsTools\Sandbox.bacpac /tsn:TargetServerName /tdn:AXDBname /p:CommandTimeout=0 /TargetTrustServerCertificate:true

As you can see, now you have to enforce trusting the server certificates to pass the error message above.

DMF TransferStatus to check entity processing

When we work with a Data Entity in D365FO, it can get tricky to track how transfers are going and how much is left. Here is a quick SQL snippet to query staging tables to figure out DMF TransferStatus:

    when transferstatus = 0 then 'Not started'
    when transferstatus = 1 then 'Completed'
    when transferstatus = 2 then 'Error'
    when transferstatus = 3 then 'Validated'
    when transferstatus = 4 then 'Duplicate'
  end AS StatusTxt,
  count(*) from [dbo].[CUSTCUSTOMERV3STAGING]
group by executionid, transferstatus

This snippet can tell us how many records have been completed, has an error, or still remaining to be processed. It only works if a staging table is being used for the data entity export/import.

Active record count in ValidTimeState tables

In the previous blog post we have explored how to tell how many records do we have per company account using a T-SQL Query. But AX does have a concept which SQL cannot cope with, and makes it a bit harder to tell the active record count in ValidTimeState tables.

The following job can pull back this value for us, in order to validate if data migration row counts are matching between AX 2012 and D365FO.

static void WIK_findValidTimeStateKey_Tables(Args _args)
    DictTable           dictTable;
    Dictionary          dict = new Dictionary();
    TableId             tableId;
    Common              common;
    date                currentDate = systemDateGet();
    setPrefix('Record count for ValidTimeStateKey tables');
    tableId = dict.tableNext(0);
    while (tableId)
        dictTable = new DictTable(tableId);
        if (!dictTable.isTmp() && !dictTable.isTempDb() && !dictTable.isView()
            && (dictTable.configurationKeyId() ? isConfigurationKeyEnabled(dictTable.configurationKeyId()) : true)
            && dictTable.isValidTimeStateTable())
            common = dictTable.makeRecord();
            select validTimeState(currentDate) count(RecId) from common;
            if (common.RecId)
                info(strFmt('%1\t%2', dictTable.name(), common.RecId));
        tableId = dict.tableNext(tableId);

SQL record count by company

As part of our Data Upgrade from AX 2012 to Dynamics 365 Finance and Operations we had to ensure a way of telling how many records do we have per data area. Data import via entities are company-based, so we had to find out a reliable way to tell if everything has been successfully transferred. The following script can tell exactly that, show the SQL record count by company:

DECLARE @tableName NVARCHAR(255);
-- Temporary table for storing record counts
CREATE TABLE #jdm_count (TableName NVARCHAR(255), Company NVARCHAR(4), RecordCount int)
-- Cursor for getting list of User-created tables
              + '.' + QUOTENAME(sOBJ.name)
       FROM sys.objects as sOBJ
              sOBJ.type = 'U'
              AND sOBJ.is_ms_shipped = 0x0
       ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name;
OPEN cur_name
-- Loop tables
FETCH NEXT FROM cur_name INTO @tableName
       -- Construct SQL Statement for getting company-specific record count
       SELECT @statement = 'SELECT ''' + @tableName + ''' AS [TableName]'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ', DATAAREAID AS [Company]', ', '''' AS [COMPANY]')
              + ',COUNT(*) AS [RowCount] FROM ' + @tableName + ' WITH (NOLOCK)'
              + IIF(COL_LENGTH(@tableName, 'DATAAREAID') IS NOT NULL, ' GROUP BY [DATAAREAID]', '')
              + ' HAVING COUNT(*) > 0';
       -- Insert statement results in temporary table
       INSERT INTO #jdm_count (TableName, Company, RecordCount)
              EXEC sp_executeSQL @statement;
       FETCH NEXT FROM cur_name INTO @tableName
CLOSE cur_name
-- Display results
SELECT * FROM #jdm_count
ORDER BY RecordCount DESC, TableName, Company
DROP TABLE #jdm_count
Go to Top