Integration Manager for Dynamics GP, maybe this is all you need

Possible connection to text file with Open Row Set construction in Transact SQL is also feasible option to combine text file with ODBC compliant database in Microsoft SQL, non-Microsoft (Oracle, MySQL, IBM DB2, etc.).  We would like to review real business case, where Linux/PHP/MySQL based custom ecommerce website was integrated via IM to GP 10.0 Sales Order Processing Invoice and Return.  Let’s take a look at details:

1. eConnect performance break through.  In version 10.0 and especially with current release of 2010/11.0 the majority of the destination mappings were enable in eConnect.  Traditional mapping was coming in OLE Server technology (where GP workstation is required to be running on the same computer and each field is entered on the form, such as SOP Invoice and then validated via user interface and finally document is saved into the batch).  eConnect is based on encrypted SQL Stored Procedures, it doesn’t require user workstation to push documents.  And as stored procedure call is done directly on the server – this technology is a way faster comparing to historical OLE server.  With new technology we see the option to call Integration in quasi real time (every five minutes) without causing bottleneck on your network and the Server itself.  With version 10.0, however we had one issue with eConnect – its IM interface was not available for SOP Return.  As return is relatively rare document type, we realized it in separate SOP integration

2. Linux/MySQL/PHP in-house programmed ecommerce shopping cart.  If you are small ecommerce retailer, you might lean toward popular shopping cart platform available on software market, such as Magento, .Net Storefront.  Larger retailers typically have to have complete control over its website and data driven shopping cart application – they might lean toward custom coding.  As our client had its ecommerce in Linux box with PHP/MySQL database we suggested establish cross platform ODBC connection and Linked Server in SQL.  We had several Agile project sprint cycles and finally decided that it is more feasible to export text files every five minutes, fire integration automatically and process exported file with placing it into the Processed folder and if any exception – log it in the Exceptions folder.  Exception log creation logic was programmed in Before Document Commit VBA event script.  Alternative way to Integration Manager would be calling eConnect from Linux via Great Plains Web Services interface (here you may expect some under the hood programming).  If you prefer Microsoft Visual Studio – you can achieve the same goal by including eConnect libraries into VS project, where you are in control (C#, VB.Net or C++ are recommended, as you can find most of the code samples in SDK for these programming languages)

3. Text Files in ODBC queries.  Sometimes customer gives you Excel file where several documents are located in the same row (let’s say column C has first doc, F the second and H the third).  You normally save Excel as CSV to be open for IM.  Now in IM we recommend you to create Advanced ODBC query with Microsoft Text driver and create the view there you are unionizing these columns (and by doing that splitting them into three separate rows)

4. EDI document header, lines and trailer parsing in SQL precise select statement.  If you are deploying advanced ODBC query in IM to be based on SQL View – then in the view itself you can deploy linked server or Open Row Set construction to open Text File (EDI often comes in text file format, but it might also be in XML)

5. Quasi real time versus true Real time document integration.  IM is traditionally considered as something that is controlled by the end user.  If you would like the integration to be executed, simply open IM and click on Run Integration (or run Integration directly from GP user interface).  This method could be categorized as on demand.  However it is possible to schedule the integration to fire every certain number of minutes to bring you over to the quasi real time categorization.  And in our opinion, the large portion of nationwide and international ecommerce merchants is deploying quasi real time shopping cart integration method (where you are getting email notification immediately and then some time later the notification that the order was submitted for fulfillment on the warehouse floor)

6. Module Licensing.  You can get it for data conversions only (first few months giving you reasonable time to complete data migration from your legacy accounting system, such as QuickBooks, PeachTree, MYOB, SAP Business One, Oracle Financials, etc.).  Or you can have it for permanent use without date restriction.  It is available for Financial series only, for Distributions (Inventory Control, Sales Order Processing, Bill of Material, Purchase Order Processing)

7. Integrating data with GP for the multinational company.  Here you need to understand if Dynamics GP is localized for the country, where your overseas subsidiary resides.  It is localized (or partially localized) in most of the English speaking countries, in Spanish speaking Latin America and Caribbean Basin, it is available in French in Quebec Canadian province.  Also we see several blends of Great Plains in Arabic speaking countries.  However please be aware that it is not localized in such regions as China, Brazil, Russia, Korea, Japan.  In fact there are some works around to make Hieroglyphs to be stored together with Great Plains document (please review NJ Star utility, or attach PDF, Word or Excel documents in Chinese as OLE Notes).  The real problem and show stopper might be the compliance to the local country tax code (especially actual in large regional powers, such as India, China, Russia, Brazil, Indonesia)

8. Beyond the Integration Manager capabilities.  We would suggest two options to review.  First would be Microsoft Dexterity – this is all mighty tool and you can do whatever you need in regards to altering existing logic or creating new logic with seamless interface (and compliant to application security realm).  Dexterity, however is not the tool recommended for overnight, over the weekend and even couple of month learning curve.  Dexterity programming is rather career dedication (where you may consider Microsoft VB coding to be normal extension to such career as SQL DBA or MCSE).  Second alternative might be direct SQL scripting – this route is for experienced Great Plains developers, who are completely familiar with tables structure and data flow (and who are comfortable to roll back data compromising, caused by direct SQL update or insert statement)

9. Integration Manager and Older Versions.  IM is available for historical versions.  We are consulting it since 1998, and respectively from the version 4.0.  This version and all the following: 5.0, 5.5, 6.0, 7.0, 7.5, 8.0, 9.0 were based on OLE Server technology (available also for such Microsoft Office Applications as Excel, Word, Access).  The real revolution is seen with the introduction of eConnect connectors with the release of version 10.0.  However OLE Server versions are available for all the mentioned above

10. If you feel that your concerns are not addressed or your question is not answered, please feel free to contact our office.  Support domestically in the USA, Canada, Mexico and internationally.  This option is possible via Web Sessions, Skype or Phone conferences and direct visits onsite (in the case of the large scale project).  Our consulting team speaks English, Chinese, Portuguese, Spanish, Russian, Filipino.  Feel free to call us 1-866-304-3265, 1-269-605-4904, or email help@efaru.com