You’re offline. This is a read only version of the page.
265
If I need to to insert 7000 invoices (each invoice has about 5-25 details) into Solomon 7.0, shall I do it using transaction import or shall I insert them into the database directly (via ODBC driver). I know using transaction import will preserve business logic and thus safer to future upgrade. However, it's pretty slow (more than 40 minutes). Using ODBC driver is fast, but I have to know the business logic behind Solomon and I'm not sure if I'll miss anything.
So what will an expert do? Or is there anything else better than these 2 methods? I have also tried Solomon Object Model and it's pretty much similar to transaction import as far as the speed is concerned.
I'll appreciate any suggestions.
Kai
*This post is locked for comments
hi Kai,
I'm new beginner with SL and I have to TI more than 270k lines of records and in total it multiply 2 for ARDoc and ARTran and few batch lines, could you explain with detail what you do with TI via Invoice & Memo Screen using ObjectModel like your post before.
Is it just a simply TI or there is an other setting you make? thanks.
Rommy
Whew! I love hearing that!!! <grin>
I end up using ObjectModel. Direct database import is kind of dangerous because I can't guarantee the business logic of SL is being followed and it might cause issues with future upgrade of SL. That's why I gave up even though speed wise, it's much better.
ObjectModel is not fast, but I can automate the process by running the program at night so that speed wouldn't be critical to me. It took me about 1 hour and 50 minutes to import 6700 invoices (50616 lines of details) via Invoice & Memo Screen using ObjectModel.
Kai
Kai,
Did you had any luck with direct database import using ODBC or SQL?
What import process have you found to be more efficient?
Any improvements in the speed you were looking for?
Have a good day!
If anyone needs a "horror story" about directly inserting into tables... we had a client who did that on the VENDOR table. Things appeared to go fine for THREE YEARS. THen we did an upgrade that affected that table. DBMaint would just not do the update of that table. Kept failing with weird error messages.
After two days of work (which we charged the client for) we discovered that they had inserted a null into one of the fields. Didn't show up anywhere else to affect processing, but apparently Solomon does not like nulls. Once we fixed that, all was well.
Thanks all for the suggestions and advice. I'm going with insert data using ado.net.
Does anybody know what value I should use for these 2 columns in ARTran table: LineID, LineNbr. Seems LineID has 2, 4, 6, 8, 10, 12, etc. and LineNbr has -32768, -32512, -32256, -32000, etc. by looking at the table after entering an invoice using SL 7.0. Is there any trick in populating these 2 columns?
Also does anybody know what CustNameXRef table is used for? When I use ado.net, I had the complete customer name (e.g. Test Customer 101) in the NameSeg column. However, when creating a new customer in SL 7.0, this table has segments of customer name (e.g. Test is the 1st record, Customer is the 2nd record, 101 is the 3rd record). I know there is a trigger for Customer table. I don't know why the triggers works differently using ado.net. The trigger ADG_TR_CustNameXref_Add will call the stored procedure ADG_CustNameXref_Add which will split the customer name into segments using blank (I think it's null when using ado.net and thus ddoesn't split the name by blanks) as delimiter (the delimiter actually comes from INSetup). I haven't been able to figure this out for months and this made me start to think about using transaction import. But I really want to use ado.net.
Kai
We also use ADO, although most often from Excel, so we typically use VBA rather than .NET.
I should also point out that we validate all data before it gets inserted, i.e. all CustIDs, Accts, Subs, Project and Task IDs, etc. are first validated and the invalid rows are spit back to the client application. Then the stored procedure still won't insert any rows with invalid IDs, because the select used with the insert has joins to the relevant master tables.
As to writing code to insert records, we do it a lot but we have been doing code like this for years and learned the hard way what to do and what to not do. That's the reason we recommend using TI or the object model for most import projects. I still think seven hours once a month is negligible. You could run the process over night. Plus the time to code up a TI process is usually much shorter than coding a program to import the data. The old cost/benefit analysis, ya know.
However, if you still want to write code, I would recommend not using ODBC, which is a 15 year old technology with limited capabilities and onerous install requirements. We do our custom code in ADO.Net using the System.Data.SqlClient class and it's really fast.
Gosh, Jonathan, that's so nice! Thanks!
You've already received replies from two of the most knowledgable Microsoft Dynamics SL partners in the world.
We have a client that imports thousands of AR invoices twice a month. They used to do this using Transaction Import and it took about an hour. We wrote a direct import for them which cut the time to about ten seconds. If you do this, I recommend bringing the data in as a batch in Hold status.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (