Great Plains Reseller: Integration Manager Advanced Techniques

With the introduction of eConnect, such tools as Dynamics GP Integration Manager, being moved to eConnect platform opened the door for you, Great Plains Programmer or Technical Consultant to really advanced, large and complicated data migrations and conversions.  The techniques, described in this article were actually available for old Integration Manager connectors, however they were not practical, until eConnect virtually resolved integration speed and performance.  With eConnect integrations you can move up to ten transactions per second, as eConnect is calling encrypted SQL Stored Procedures, comparing to validating GP logic in OLE driven Great Plains Dexterity forms in old versions of IM.  Let’s consider scenario, when you have to convert the whole year of GL records for large company with several thousand transactions per week.  Here is what you do:

1.       Export data in tab or comma delimited text format and import it into Microsoft SQL Server in the form of staging tables.  Then massage the data in the tables: remove quote marks, for example, where you have Debit and Credit fields.  Next step – consider creating SQL views, which will be the base for Advanced ODBC queries in the next paragraph

2.       Advanced ODBC queries, based on SQL ODBC driver.  You can use Dynamics GP DSN for elegancy.  If you already tried to group, aggregate data in ODBC queries, based on Text Driver, you probably had very disappointing experience.  You should be surprised – in the case of Dynamics GP DSN you do not see those annoying error messages, attributed to poor Text Driver

3.       GL Header Source.  Select transaction number only, plus alternatively use MAX construction to add Transaction date and group by transaction number

4.       GL Lines Source.  Simply use select star statement from your SQL view.  Then link two sources by transaction number field and potentially preview both sources to be sure you are on the right side of the road

5.       Save and Run Integration.  Please, note here, that eConnect integration connector doesn’t require you to close all GP windows (as you remember OLE connector required).  If you are moving huge volume of transactions, you can look at them showing up in GP while integration is in progress

6.       Now you should be at the point, where IM helps you do what you were doing in the past with low level insert scripts and obviously were fighting with data integrity.  As you know IM validates business logic and doesn’t let inconsistent records move in

Source by Andrew Karasev