I’m new to nav and this forum and I want to introduce me a my current project a bit first. We have bought Nav 2009 for our company a while ago and now we want to automate some data uploads into Nav. I’m the guy who is in charge to consider a solution and as a SSIS (SQL Server Integration Service) advance user I would like to use this tool also for uploads into Nav. Due to a other project we have bought third party tools (http://www.cozyroc.com/)) which contains the possibility to connect a sql server database with nav over a web service.
I was able to create and configure web service in nav and I can display it in IE.
I have understood that I have to create single pages or code units in nav on the web services page which will be then accessible over the web service.
Now here comes the problem:
My current project is to migrate accounting data from a sql database to nav. I have identified the target table in the nav sql db, let’s say [dbo].[CRONUS ltd_$_G_L Entry], and the corresponding object in nav should be a page called General Ledger Entries. So I have created a web service which uses the ID of this page. I can see now the web services as above described in IE but unfortunately I can see there only the method read, so no create or update methods are possible. So when I now use SSIS with the cozy roc tool I can only read data with the General Ledger Entry web service, I cannot use this web service to insert data.
So my question is now, how can I change a web service or the corresponding page in nav to have create and update methods?
As further information, the page is configured as list, not sure if this is important. I could imagine this brings some restrictions.
Apologize for this long post but I think this makes my problem more clear.
Welcome to the user group. I think the reason you haven’t received any answers here is that most people here have no idea what you’re talking about. You’re obvious an experienced SSIS user, but I don’t think you will find many experienced NAV consultants who would consider using SSIS to a migrate data into NAV. And especially not directly into any of the Entries tables!! (I also wrote a little about this in my most recent blog post - the link is in my signature).
When migrating any “already posted” data like general ledger entries, then you should always go via the general journal line and use NAV to create the new general ledger entries. And you should use the system to verify the content of all the lines which are imported. Otherwise you will easily risk that data will end being corrupt and additional required tables not created.
Therefor these tables are generally made so that you cannot write to or modify these tables directly, only indirectly via code or posting.
In general I would suggest using the build-in RapidStart tools in NAV for most migrations. It’s much easier and you’re able to assure that the fields are verified and data imported correctly. And if you’re using XML files instead of the easier Excel files, then the it will also be very fast.
The members and moderators here on DUG use our spare time and help you for free, all we ask in return is that you spent a few seconds to let us know if we helped you solve your problem.
If any of the suggestions helped you solve your question/problem, then please click the “Verify Solution” on the answer that helped you, so that we can see that your problem is now “resolved”. Otherwise let us know how if there is something else you like to know.
sorry for my late reply, but I had a longer vacation and I’m just back in my job.
And yes, your answer pointed me in the right direction. After a talk with our Navision consultant, we could find out where the data in the back end needs to be placed. It is the table “$Gen_Journal Line”, here we need to import the booking data. After a few tries, it seems there are no strong dependencies between this table and others, just some id’s needs to be mapped to a corresponding integer ID. I cannot see any reason why I shouldn’t migrate data directly into this table. I can see the migrated data in the General Journal mask in Navision and I can post the data if everything is correct migrated.
I think your suggested Rapid Start Tools are only in 2013 available so I will still use SSIS to migrate the data directly into the back end table. I have already developed a small package in SSIS where migrates the data from a raw data table into the $Gen_ Journal Line table from the CRONUS Company and our Finance team was happy with the result.
Now the next steps are to implement some validation and notification services around the migration process. But this is more a SSIS issue and I think that’s it for the moment with my Navision questions. Maybe there will come up some other question in the near future because my boss have some other projects in mind regarding data migration and Navision. You will hear from me again soon [;)]
Happy to have been able to help you. Yes in NAV 2009 you don’t have the RapidStart tools. But you still have the same functionality to import data and here it’s called something as clear as “Data Migration” and it still allow you to import in the exact same way.
just I’m interested, would it be possible out of Navision 2009 to create something like a job what uploads automatically xml files in periodic times and send emails when the upload process is finished? And would this be also possible in the other direction, to specify a directory where nav automatically downloads data tapes with specified content like booking journals? That would be helpful to know.
You already have the Job Queue. If you create your import process as a codeunit then can also have this function send you an email for every time it runs. This job/codeunit could then do the whole process. Import the data into the Gen. Journal Line table and when imported posts the lines. And finally send the confirmation email.