Our support engineers have assembled the top recommended solutions for you.
Microsoft Dynamics AX 2012CRM Connector in Microsoft Dynamics AX 2012Financials Management in Microsoft Dynamics AX 2012Upgrading to Microsoft Dynamics AX 2012
Microsoft Dynamics AX 2009
Application Object Server (AOS)
Enterprise Portal and Role Centers
Inventory Costing in Microsoft Dynamics AX 2009
Invoice Settlements/Discounts/Reversals
SSRS and SSAS Integration
Workflow
Hi,
I'm looking for assistance with safely importing data into AX 2012 using SQL but there doesn't seem to be that kind of depth here on this forum.
So where does one go for help?
I've been searched through the documentation and that section seems to have not been written. Most attempts at importation can't be safely and completely removed short of a full database restore which becomes increasingly difficult when you have teams working on their own specific sections.
Thank you in advance for your unexpected response.
Again, in previous versions this was not as big a deal, but in 2012 it's really tough to do.
If you are running the CU3 update, you can use the SysDatabaseTransDelete class. It's not in the menu anywhere, but it's in the AOT and you can run it. It is supposed to remove ALL transactions (journals, SO, PO, inventtrans, ledgertrans, all those types of tables). Once that is done, AX should no longer have a problem with you deleting master data due to existing transactions. Note that this class was broken in early releases in 2012, and was supposedly fixed in CU3. We used this class heavily in previous releases of AX but have not been using it in 2012 since it wasn't working early on. I have no personal experience with this in CU3 versions, as we just moved on and are doing database restores (let's just say our confidence was shaken after 2012 was released).
Of course, that leaves the master data removal problem still which you have described well. The only 100% sure way that data is removed cleanly is from the UI. Unfortunately, this takes forever and isn't really a viable option. One option you can try, which I haven't tried in an actual implementation, is to use the same strategy as the SysDatabaseTransDelete class and auto-remove all tables of a certain table-type (if you look at the code for sysdatabasetransdelete you'll see what I mean). This should be one way of removing all data but leave parameter and group tables.
I would indeed strongly advise you to have a separate environment and just restore a database upto a point that works for your migration scripts. As far as licensing you should not be worried, since these are temporary environments for non-production use, there is no issue. This is done all the time by everyone everywhere, including Microsoft Consulting Services.
Dynamics AX MVP | My Blog | Sikich | Twitter @JorisdG
Hi Kimo,
I'm surprised by your candid feedback about the forum. Some of the best AX technical people I know do visit these forums frequently so I would be surprised if collectively we can't get your questions answered.
Let's break down your question so we can get more depth going for you.
Considering you say importing into AX using SQL, you are going through SQL and not AX. If this is incorrect, please rephrase your question.
As you most likely know, AX abstracts database operations from SQL, and even fails to provide most meta data in the SQL realm (no FK relationships and things like), let alone provide business logic. On top of that, AX keeps track of internal record identifiers (rec ID - this can be taken care of from SQL, but the few people that know how to this are somewhat afraid to share this knowledge as this can be easily done wrong and render an entire database inoperable).
Besides the obvious RecID problem, the table relationships and functionality that only reside in AX present a big enough problem to avoid messing with SQL directly at all cost.
Now, what are you trying to do exactly, which tables are you importing, what tools are you using (SQL integration services?), ...?
You are correct that removing data imports to try them again is difficult. Most implementation companies I know (definitely in AX 2012 - 2009 was a bit easier) have a separate environment to test migrations in so that you do not bother other people working in the environment. A quick database restore is then easily accomplished.
So, I'm sure we'll get your question answered, but you do need to ask the question first. A lot of times on these forums there are no in-depth answers because the questions are not in-depth. Most of us ARE in fact willing to share knowledge, but the one asking the question needs to be willing to share the details of what they are doing.
Joris,
Give me a bit and I will articulate my question better; this was a feeler to see if anyone was actually available to offer anything. Importing data seems to be the biggest problem I see with DAX.2012.
The tools that were mediocre in 2009 are even less functional in DZX 2012.
We are writing custom x++ code and creating our own classes and custom projects to read comma delimited files to populate tables and tracking our record IDs but, we observe once data has been imported, it is extremely difficult to validate data removal if we need to modify the imported data or strip it out and start over.
We have some routines to remove data but, we are only 90% sure that the data is removed. And, if the record has been used in a transaction (ie., SO, PO) it's near impossible to remove the underlying data if it needed to be changed especially since many tables are no longer company specific and some tables have no company association (dataareaid).
I take you point that I need to add an additional AOS and testing environment; now I need to check the customer's license.
So far, even with a test environment; it still looks like you have to dump and reload from scratch every time.
Giving you the credit for this but, reserving that I may be back for additional direction.
Thank you!!