4

I need to pull data from a single table stored in a MSSQL database. The data then gets stored in a staging table in AX, from where it is processed according to the business logic.

Performance is a key factor in this project.

Now I was doing some research of what are the possibilities to retrieve data from an MSSQL database with X++ code and found following blog:

Connecting to Databases through X++

Basically there are different ways which can be used for this endeavour:

  • ODBC - Open Data Base Connectivity
  • OLEDB - Object Linking and Embedding
  • ADO - ActiveX Data Objects
  • Connection Class

Now I was hoping that someone could give input of which one is preferrable and why (especially in regards to performance).

Any input is appreciated.

elToro
  • 1,003
  • 9
  • 31

2 Answers2

8

The connection class isn't a valid option because it connects to the same database AX is connected to, so your choices boil down to ODBC, OLEDB and the ADO/.NET way.

Personally I would go for the .NET integrated way (called OleDB in your link) but would use System.Data.SqlClient instead of System.Data.OleDb because it's natively written in .NET. If your code is compiled to CIL that should give you best results.

No matter what option for the retrieval you pick, you should really be building a recordinsertlist and perform a set based insert instead to optimize insert performance.

Tom V
  • 1,498
  • 18
  • 24
  • I implemented the task using OLEDB. Also took a look into DIXF. At the end I decided to go with OLEDB and implementing the SysOperation Framework, so the code can be executed as batch job on the server. It seemed like less work and having more control over what is happening. This solutions performance totally meets the projects requirements. – elToro Feb 11 '16 at 08:36
5

This sounds like a job for the Data Import/Export framework within Ax 2012.

Reinard
  • 3,624
  • 10
  • 43
  • 61
  • Thank you for your response. This is also very good input. I didn't thought into this direction. Doing some googling I found a Walkthrough for DIXF with ODBC https://ax.help.dynamics.com/en/wiki/walkthrough-import-data-from-an-odbc-data-source-dixf-dmf/ . I'm currently not clear which are the advantages/disadvantages of using DIXF over importing from the database by implementing a connection (like ODBC or OLEDB) in a class. – elToro Feb 03 '16 at 07:45
  • 2
    Hi, I upvoted your answer as it is a very good option I failed to consider. Could you please elaborate a bit on how this would work? Maybe it's sufficient for the OP and me, but an answer with more detail might convince others why it's helpful and a good solution (possibly better than mine considering the AX version). – Tom V Feb 04 '16 at 19:21