RSS

Category Archives: SSIS

Generate Next RecId for Dynamics AX Table from SSIS

If you want to insert records directly into the SQL database using SSIS, you cannot on-the-fly get RecId’s easily from SQL manipulation alone, so another option is reserving the Recid’s using x++ in an x++ job or service, for example:
To get the next Recid for SalesTable,use the below code and before printing the recid , suspendrecids() has to be call then after printing recid removeRecIdSuspension() method should be call….

static void getNextRecId(Args _args)
{
//Table that stores record ids details for tables
SystemSequences systemSequences;
Counter noOfResereveRecId;

///Suppose you wants to reserve 40 records

noOfResereveRecId = 40;

//Class that handles Record id generation
SystemSequence systemSequence = new SystemSequence();
;

select firstonly systemSequences
where systemSequences.tabId == Tablenum(SalesTable);

systemSequence.suspendRecIds(systemSequences.tabId);
info(strFmt(‘Next record id: %1’,systemSequence.reserveValues(noOfResereveRecId, systemSequences.tabId)));
systemSequence.removeRecIdSuspension(systemSequences.tabId);
}

This job will take a table, and reserve one recId to be used that the system will simply ignore/skip. A word of caution, if you accidently reserve a huge number it’s not easy to get those reservations back, and int64’s do have a limit (albeit a huge number).

You could probably create a service in x++ that you could hit from your SSIS job where you tell a table name and an amount to reserve, and get an int64 back, that way you could automate the SSIS job.

Advertisements
 

Tags: , ,