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
///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);
info(strFmt(‘Next record id: %1’,systemSequence.reserveValues(noOfResereveRecId, 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.