Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / QueryBuildDataSource c...
Finance | Project Operations, Human Resources, ...
Unanswered

QueryBuildDataSource container as range

(2) ShareShare
ReportReport
Posted on by Jesús García 305
Hi all, i'm trying to add a range in a querybuilddatasource using a container that 'contains' recids to filter. I tried using this line:
qbds.addRange(fieldNum(COVBKSCustTransOpenEntity, RecIdCT)).value(queryValue(con2Str(subContainer)));
I add this filter to do a DMF export but it ignores this filter. It only works when i use this alternative :
qbds.addRange(fieldNum(COVBKSCustTransOpenEntity, RecIdCT)).value(con2Str(subContainer));
But sometimes the export is failed by an error like this
BYOD export failed on final attempt

Error al agregar componente de flujo de datos para la entidad de almacenes
<?xml version="1.0"?><Errors><Error><ErrorCode>-2146233088</ErrorCode><SubComponent>Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper</SubComponent><Description>DMF1987 -    at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowComponent(Package package, String commandText)
   at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFPackageControllerEntityDB.CreateAndExecutePackage(DMFEntityDBHelper entityDBHelper)
   at DMFExecutePackage.PackageCreator.processByodEntity()
   at DMFExecutePackage.Program.Main(String[] args)</Description></Error><Error><ErrorCode>-1071636471</ErrorCode><SubComponent>Microsoft.SqlServer.DTSPipelineWrap</SubComponent><Description>Exception from HRESULT: 0xC0202009 -    at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
   at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowSourceComponent(Package package, String commandText, MainPipe dataFlowTask, IDTSComponentMetaData100&amp; rowCountComponent)
   at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowComponent(Package package, String commandText)</Description></Error></Errors>
 
So, is there any way to add a container like a range and DMF could accept like a filter ?
Categories:
Dynamics 365 Finance
Microsoft Dynamics AX
  • Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    QueryBuildDataSource container as range
    What you do with RecId is a major problem for performance. If your reason for doing it was to improve efficiency, it's time to throw the solution away. That will be a solution for this thread too - the problem will simply cease to exist.
     
    If you want to discuss it futher, please clarify another thing. Do I understand correctly that you aren't actually talking about BYOD feature of F&O and you're just reusing the same for your own solution to export the data? Or what is the relation of your batch jobs with BYOD?
  • Jesús García Profile Picture
    Jesús García 305 on at
    QueryBuildDataSource container as range
    Okey, sorry if i dont explain correctly my situation.
    The reason why i chose filter using recid is efficiency, but maybe i was wrong using it.
    Regarding the second question, well, when i export the data to external database i have to tables: the first one when i truncate all data and insert new export, and the second one, the final table, where i insert in an incremental manner. I do this to maintain data integrity in case there is an error in the export and not all cases have been exported.
     
    The scenario is as follows: I have been tasked to perform a solution in D365F&O where I have to perform a series of exports related to several tables, which contain information needed by another team in the department. Within the solution I have two different parts, making use of the SysOperation Framework: On the one hand the ExportController,service... class that is in charge of creating the project in the dmf, adding the entity, configuring the export, etc. It obtains any entity previously created and simply exports it. On the other hand, for each entity I need to export, I create another set, for example CustInvoiceJour (CIJController,CIJService...), where I take the ‘picture’ of the index fields, add the RECIDs as a filter to export the entity, and finally I call the ExportController class that is in charge of exporting the entity. Roughly speaking, that would be the scenario.
  • Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    QueryBuildDataSource container as range
    That doesn't explain why you do it all. Why can't you "select custinvoicejour using a date" when exporting the data? Also, why don't you simply export all invoices (in an incremental manner)?

    Please give us at least some information about your business scenario. Discussing little implementation details isn't useful if the overall approach may be wrong.
     
     
  • Jesús García Profile Picture
    Jesús García 305 on at
    QueryBuildDataSource container as range
    The steps are (using CustInvoiceJour for example):
    - While select custinvoicejour using a date to get the index field from table
    - store this index fields in another table using insert_recordset
    - we use the recid field from index to configure entity table and export it by DMF
  • Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    QueryBuildDataSource container as range
    What query do you use to create the snapshot? Can't you use for the export too?
     
    How do you store "the snapshot"? If you store RecIds in a table, you can use a join to build the filter. It'll still be very inefficient, therefore it's not something I recommend. But it would still be more efficient than your current approach and it'd support any number of values. It may be a temporary solution before a fixing the design issue properly.
  • Jesús García Profile Picture
    Jesús García 305 on at
    QueryBuildDataSource container as range
    Well, in general terms, our development is responsible for exporting to an external database for use by another department in the company. For this purpose, we have developed a solution where: 
    - We create entities (CustInvoiceJour, CustInvoiceTrans, TaxTrans, etc).
    - We take a ‘snapshot’ of each table between two dates and store their indexes to keep the data and from this information, we export the entities filtering through the RecId field of each one of them. This means that if I take a ‘snapshot’ of yesterday's CustInvoiceJour and save 5 lines, in the part of the development where we configure the entity before exporting it, it will add 4 addRange(...). If it's 50000, you can imagine the rest.
  • Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    QueryBuildDataSource container as range
    Do you mean that you add 300000 range values? If so, it's no wonder that it fails - it's a bad design. If you explain your business requirement to use, we can help you come with a reasonable technical solution.
  • Jesús García Profile Picture
    Jesús García 305 on at
    QueryBuildDataSource container as range
    Well, it works when i add like 300-400 RecId in the container that i use to add filters to QueryBuildDataSource. In that case, i developed a entity base on CustTrans, and then, i configure the entity by code to export it using DMF. i add this container like filter to export only the registers with the same RecId. Like i said, when i dont use a lot of recid, it works but when i export like 300000 (equals to a year), this error appears. 
  • Martin Dráb Profile Picture
    Martin Dráb 230,605 Most Valuable Professional on at
    QueryBuildDataSource container as range
    How do you use your code when exporting data to BYOD?
     
    Is the error related to the code at all? You said it sometimes fail, which means that there are cases when the code works fine. Are you sure that it never fail without the code and it fails sometimes only when your code is there? Also, tell us more about "sometimes". Does it depend on what data you export or it sometimes work and sometimes not even if you export exactly the same records?
  • Jesús García Profile Picture
    Jesús García 305 on at
    QueryBuildDataSource container as range
    BYOD export failed on last attempt
    Error adding dataflow component for warehouse entity
    <?xml version=‘1.0’?><Errors><Error><ErrorCode>-2146233088</ErrorCode><SubComponent>Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper</SubComponent><Description>DMF1987 - at Microsoft. Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowComponent(Package package package, String commandText)
       at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFPackageControllerEntityDB.CreateAndExecutePackage(DMFEntityDBHelper entityDBHelper)
       at DMFExecutePackage.PackageCreator.processByodEntity()
       at DMFExecutePackage.Program.Main(String[] args)</Description></Error><ErrorCode>-1071636471</ErrorCode><SubComponent>Microsoft.SqlServer. DTSPipelineWrap</SubComponent><Description>Exception from HRESULT: 0xC0202009 - at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
       at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowSourceComponent(Package package package, String commandText, MainPipe dataFlowTask, IDTSComponentMetaData100&amp; rowCountComponent)
       at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFEntityDBPackageExport.AddEntityDBDataFlowComponent(Package package package, String commandText)</Description></Error></Errors>.
     

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey Pt 2

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,904 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,605 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans