Skip to main content

Notifications

Microsoft Dynamics SL (Archived)

transaction import versus ODBC driver

Posted on by 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

  • Rommy Petrus Profile Picture
    Rommy Petrus 5 on at
    Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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

  • Toni Savage Profile Picture
    Toni Savage 260 on at
    Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    Whew!  I love hearing that!!! <grin>

  • Kai Wang Profile Picture
    Kai Wang 265 on at
    Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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

  • Captain Faraz Profile Picture
    Captain Faraz 20 on at
    Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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!

  • Toni Savage Profile Picture
    Toni Savage 260 on at
    Re: Re: Re: transaction import versus ODBC driver

    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.

     

  • Kai Wang Profile Picture
    Kai Wang 265 on at
    Re: Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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

  • Re: Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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.

  • pbrattin Profile Picture
    pbrattin 1,850 on at
    Re: Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver
    Gee! Thanks from Price too.

    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. 

     
  • Laura Barber Profile Picture
    Laura Barber on at
    Re: Re: Re: Re: Re: Re: transaction import versus ODBC driver

    Gosh, Jonathan, that's so nice!  Thanks!

  • Re: Re: Re: Re: Re: transaction import versus ODBC driver

    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.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,963 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans