web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / .NET Business Connecto...
Microsoft Dynamics AX (Archived)

.NET Business Connector and SQL Server Integration Services (SSIS)

(0) ShareShare
ReportReport
Posted on by Sivasankar Elumalai 305

Hi,

I have created an SQL Server Integration Services 2005 package which connects to Dynamics AX 4.0 with the help of .NET business connector. The SQL SSIS package connects to AX and pulls the necessary information correctly and it has no issues in retrieving the data and saving it into a new SQL Server database. This new database is different from AX database.

The issue is with the performance of .NET Business Connector. It seems to be too slow. It takes about one hour to retrieve 700 thousand+ records. Code in AX uses three tables (CustInvoiceJour, CustInvoiceTrans and InventDim) and uses the AX default relations to retrieve the data and send it back to the SQL SSIS Package.

I know this is can be done using SQL but I believe that is not advisable as some of the business logic in AX cannot be recreated in a simple SQL statement. I could be wrong on this assumption.

Could someone please suggest how to improve the performance of NET Business Connector?

Thanks for your help.

Siva.

*This post is locked for comments

I have the same question (0)
  • Dick Wenning Profile Picture
    Dick Wenning 8,705 Moderator on at

    be aware that AX uses dynamic queries and heavely use the tempDB

  • Community Member Profile Picture
    Community Member on at

    Hi,

    I think this is not performence issue of .Net Bussiness connector.It depends upon your logic ,the way you are pulling data.Let us know first your logic..

    Regards

    Jagyan

  • Dick Wenning Profile Picture
    Dick Wenning 8,705 Moderator on at

    please turn long running queries on for the proxy or inpersonate user, set the query limit om 3000 ms and store it in the DB for details http://axstart.spaces.live.com/blog/cns!E82F2C8CB173C0A0!263.entry

     

     

  • Sivasankar Elumalai Profile Picture
    Sivasankar Elumalai 305 on at

    Hi Guys, Thanks for your replies.

    Code logic is as follows:

    • I have an AX query which joins the three tables CustInvoiceJour, CustInvoiceTrans and InventDim (in that order) using their default AX relations.
    • I have an SSIS package which connects to AX using .NET Business Connector and uses the following code to retrieve the query data.

    Dim tblCustInvoiceJour As AxaptaRecord = axInstance.CreateAxaptaRecord("CustInvoiceJour")
    Dim tblCustInvoiceTrans As AxaptaRecord = axInstance.CreateAxaptaRecord("CustInvoiceTrans")
    Dim tblInventDim As AxaptaRecord = axInstance.CreateAxaptaRecord("InventDim")
    Dim queryInAX As AxaptaObject = axInstance.CreateAxaptaObject("Query", "QueryInAX")
    Dim queryRun As AxaptaObject = axInstance.CreateAxaptaObject("queryRun", queryInAX)

    While CBool(queryRun.Call("next"))
         tblCustInvoiceJour = CType(queryRun.Call("get", tblCustInvoiceJour.Field("tableid")), AxaptaRecord)
          tblCustInvoiceTrans = CType(queryRun.Call("get", tblCustInvoiceTrans.Field("tableid")), AxaptaRecord)
          tblInventDim = CType(queryRun.Call("get", tblInventDim.Field("tableid")), AxaptaRecord)

            InvoicesBuffer.AddRow()
            InvoicesBuffer.DealerNo = tblCustInvoiceJour.Field("InvoiceAccount").ToString
            InvoicesBuffer.InvoiceDate = Convert.ToDateTime(tblCustInvoiceJour.Field("InvoiceDate"))
            InvoicesBuffer.SaleableItemNo = tblCustInvoiceTrans.Field("ItemId").ToString
            InvoicesBuffer.Quantity = Convert.ToDecimal(tblCustInvoiceTrans.Field("Qty"))
            InvoicesBuffer.LineAmount = Convert.ToDecimal(tblCustInvoiceTrans.Field("LineAmount"))
            InvoicesBuffer.CurrencyCode = tblCustInvoiceTrans.Field("CurrencyCode").ToString
    End While

    • The data retrieved from AX is then passed on to the next data flow task to feed into the SQL Server table.

    I hope this helps.

    Dick Wenning - I will try your suggestion to monitor the long queries. Thanks for the link.

    Please let me if you have any better suggestions. For now, I am using an SQL query running against AX database to pull the above data. This SQL query uses the same logic as AX query. So far it seems to be working fine.

    Thanks,

    Siva.

  • Dick Wenning Profile Picture
    Dick Wenning 8,705 Moderator on at

    use the next command :Query.datasourceNo(1).tosrting() this will show the query, perhaps you miss some joins.

    have a look at the next example of the end of this article.

     

    http://axstart.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=cat%3dAX%2b2009

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans