How to manage the RecIds during a data import from SSIS?

(0) Share

Report

Posted on by Community Member

Hi,

I am building an SSIS project to transfer data from CRM Online and from an internal web application into AX 2012 database.

During the import I experienced the following problem:

While the records are imported successfully into AX 2012, I receive an error message whenever I try to create the same type of records from the UI, which persists until I delete the imported records. From what I read it is related to RecId duplication (apparently the system is trying to create the record's row with the same RecId that I used during the import).

I followed this article below to try to update the NextVal field of the SYSTEMSEQUENCES table (where the next RecIds for each table are kept), but I still receive this error.

https://sumitsaxfactor.wordpress.com/2011/04/01/handling-recids-in-sql-server/

Can you suggest me any other approach through which I can manage the RecIds and avoid the problem I described?

I have also read that I can "reserve" certain number of RecIds through using X++ code and then calling the X++ service in my SSIS project, still I am trying to avoid that approach for the moment, as we want to keep the logic "SSIS only" as it would be easier for the other .NET Developers in the team, who are unfamiliar with AX 2012 and X++, to support it, as it's pure C# and SQL code for the moment.

Yet, if there is no feasible way to use only SSIS to fix this issue, what else would you suggest me to use?

Thanks.

Best Regards,

Evgeni Dyulgerov

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

Martin Dráb 237,953 Most Valuable Professional on at

Like (1)

Report

Never meddle with AX database directly. As you see, destroying the database is extremely easy if you bypass all business logic.

Instead, use the Data Import Export Framework (DIXF) inside AX, which can gracefully handle everything for you. It's not just about RecIds, but also data areas, validation logic, number sequences, table inheritance and so on.

Was this reply helpful? Yes No
Crispin John Augustine 37,081 on at

Like (0)

Report

I'll take Martin's recommendation..

Just a further info, that you mentioned "easy for .net developers". I am sure, a dotnet developer is happy to fill a staging table in another DB (not AX DB), or in a file. You can use batch processing to check for data in the staging table / file to import to AX using either DIXF or Excel import or other ways..

This works disconnected, but without a Problem.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you for your answer.

Is there a way I can use DIXF through an SSIS package, in order to keep the current approach with the SSIS?

The goal is to have an automatic import of data in AX 2012, every 2 hours or so. I will start researching how I can do that with DIXF, but I am wondering whether that can be achieved with SSIS and DIXF working together?

Best Regards,

Evgeni Dyulgerov

Was this reply helpful? Yes No
Verified answer

Crispin John Augustine 37,081 on at

Like (1)

Report

With DIXF, you can setup different batch jobs for different groups of data, at different intervals..

I guess you can use SSIS form the external app until a intermediate table Level.

For the final step to import in AX, DIXF alone is sufficient

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,953 Most Valuable Professional on at

Like (1)

Report

DIXF already uses SSIS to extra data from data sources (including flat files), therefore you're trying to add a thing which is already there. DIXF adds AX-specific logic and configuration, including the ability to run imports on schedule, therefore you have there all you need.

Was this reply helpful? Yes No