Dynamics 365 for Operations : Connect to BYOD(Azure SQL) using x++ code:
Sukrut Parab
71,741
Moderator
Like
Share
ReportWith dynamics 365 for operations you can export data entities to your own Microsoft Azure SQL database. This feature is also known as bring your own database(BYOD).More information on this topic can be found here
BYOD is heavily used by analytics team for different financial reports. Sometimes data on those report does not tally to AX. The purpose behind writing this code is two verify record count between AX table and table for published entity in BYOD and notify admin if there is any mismatch.You can utilize this in batch job to send alerts if you found any mismatch. If publish job fails there is no way Dynamics 365 for operations notifies you automatically unless you go and check your configured data projects.
Below runnable class shows how can you connect to BYOD. It uses same connection string which you set up in Data management > configure entity export to database
using System.Data.SqlClient;
using System.Text;
class ConnectToBYODB
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
DMFDataSource DMFDataSource;
CLRObject dmfEntityProxy;
CLRObject dmfExceptionData;
DMFEntity DMFEntity;
SqlDataReader reader;
Common Common;
FieldId fieldId;
Connection conn;
str connectionStr;
str sql;
#define.serviceHelper('Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DMFSharedTypeServiceReference.ServiceContractClient')
fieldId = fieldNum(DMFDataSource, EntityStoreConnectionString);
boolean RecIdEnabled;
Recid MaxRecid;
Select DMFDataSource where DMFDataSource.SourceName == 'AnaLyticsUAT';
common= DMFDataSource;
try
{
if (!DMFEntityDbExporter::isEntityStoreEnabled())
throw error("EntityStore Not enabled");
if(!DMFDataSource.recid)
throw error("No Valid BYODB datsource provided in setup");
if(!DMFDataSource.EntityStoreConnectionString)
throw error("Connection String for BYODB is Empty");
dmfEntityProxy = new CLRObject('Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DmfEntitySharedTypesProxy');
dmfExceptionData = new CLRObject('Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.ExceptionHandler.ExceptionData');
System.Type serviceClientType = CLRInterop::getType(#serviceHelper);
str vsAssembliesPath = xInfo::directory(DirectoryType::Bin);
str configFilePath = Microsoft.Dynamics.IntegrationFramework.ServiceReference::GetConfigFilePath(serviceClientType, vsAssembliesPath);
CLRObject serviceContract = Microsoft.Dynamics.IntegrationFramework.ServiceReference::CreateServiceClient(serviceClientType, configFilePath);
dmfEntityProxy.set_ClientProxy(serviceContract);
if (common.RecId > 0 && common.orig().(fieldId) == common.(fieldId))
{
SysEncryptionLog::logStringField(common, fieldId, ReadWrite::read);
}
connectionStr = appl.DecryptFromStringForPurpose(common.(fieldId), common.encryptionPurpose(fieldId));
if (!dmfEntityProxy.TestEntityDBConnection(connectionStr))
throw error(strFmt("Connection to BYODB %1 failed",DMFDataSource.SourceName));
conn = new Connection();
using (SqlConnection connection = new SqlConnection(connectionStr))
{
connection.Open();
select firstonly DMFEntity where DMFEntity.EntityName == 'BatchJobEntity';
{
sql = "SELECT count(*) from "+ DMFEntity.EntityTable ;
SqlCommand command = new SqlCommand(sql, connection);
reader = command.ExecuteReader();
reader.Read();
info(strfmt("Number of Records %1",reader.GetInt32(0)));
reader.Close();
}
}
}
catch(Exception::CLRError)
{
CLRObject ex = CLRInterop::getLastException();
if (ex.get_InnerException())
{
ex = ex.get_InnerException();
}
if(ex)
{
error(ex.get_Message() );
}
}
}
}
Comments
-
-
Hi Sukrat,
Can you confirm if the code above will allow a direct connection to the AxDB to retrieve the record counts for tables such as the LedgerJournalTrans and GeneralJournalAccountEntry and so on. I see the connection in the code for the BYOD but not the AxDB. Also, these underlined tables are what we have our custom entities build upon and the issue of verifying the records counts between the AxDB and the BYOD as you mentioned is the main issue that we're trying to resolve. We are running incremental exports but sometimes the reports are reflecting variances between the two and then we have to do a full push export to resolve the issue. I'm not an experienced X++ developer and would like your help to get this implemented . Would you mind to communicate through person email?
Thanks,
Ricky
email: ricky.bennett5@yahoo.com
*This post is locked for comments