web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / transaction import ver...
Microsoft Dynamics SL (Archived)

transaction import versus ODBC driver

(0) ShareShare
ReportReport
Posted on by Kai Wang 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

I have the same question (0)
  • pbrattin Profile Picture
    pbrattin 1,850 on at
    Re: transaction import versus ODBC driver

    One question and one comment.

    The question: When you say "nsert them into the database directly (via ODBC driver)", how are you doing that? What process? What tool?

    The comment: I don't think 40 minutes is very long for 7000 invoices. TI is the safest way and the easiest to upgrade. I would go with the TI if it works as you want. 

     

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

    I'm a programmer (not familiar with VBA though), so I wrote my own program to insert the records directly into these database tables: Batch, RefNbr, ARTran, ARDoc. The issue is I kind of guess these are the tables affected when entering an invoice on Invoice/Memo screen in AR module. But I can't be 100% sure and I'm not quite sure about the logic of some columns in the table (e.g. LineId, LineNbr, LineRef in ARTran table).

    I made a mistake on how long it takes to import 7000 invoices using transaction import. I just used transaction import to import 7000 customer and it took more than 2 hours. That's too long to bear with. I haven't tried to import invoices yet, but I'd think it'll take at least the same amount of time as customers. I think it would be longer considering each invoice has some detail entries.

    Kai

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

    Hi,

    I have to agree with Price.  I don't think 2 hours is excessive for 7000 customers.  TI is safe and it is supported. With the time you spend figuring out the correct tables - you could have had the invoices in already with TI too!  :-)

    Good luck!

    Laura

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

    I got your point. However, importing invoices is not a one time only thing. I've just tried to import 2000 invoices and it took 1 hour and 40 minutes. So it'll probably take 6 hours to import 7000 invoices. If I need to do this every month, that's kind of impossible to implement.

    Does anyone ever try to insert records directly into database tables in solomon? I just need some kind of way to ensure the business integrity in solomon when I do the direct insertion into database. Is it more like a question for microsoft partners?

    Kai

  • Jonathan Van Houtte Profile Picture
    Jonathan Van Houtte 680 on at
    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.

  • 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!

  • 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. 

     
  • Jonathan Van Houtte Profile Picture
    Jonathan Van Houtte 680 on at
    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.

  • 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

  • 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.

     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans