Rose Business Solutions Dynamics GP

Integration Manager (IM) is a common choice for integrating data into Microsoft Dynamics GP. IM has several hooks into the Great Plains modules include the Financial, Payables, Receivables, Sales Order Processing, Payoll, Inventory, Project Accounting (via eConnect), Fixed Assets (via eConnect), and can even be used to update exchange rates. The tool allows users to define named integrations, source locations, source relationships, and target field mappings.

Integration Manager’s general functionality is to take mapped data and validate it using Great Plains business logic. If the validation succeeds, IM creates the Great Plains records and uses system automation logic to perform subsequent calculations (like the sales tax engine or auto creation of general ledger distributions).

Integration Manager expects that the user be involved, and actually requires the push of a “Run” button to initiate the integration. There are no scheduling or service components to Integration Manager. IM is a great tool for small business integration, and is perfect when user involvement and manual data manipulation is acceptable.

Since the “IM Process” is manually driven, how can we make it easier for the user? Well, I believe one place to look is in the management of source locations. Most IM integrations are set up using the standard delimited text source (CSV, Pipe Delimited, etc..). Setting up integrations like this can be inconvenient for multi-record transactions, like Sales Order Processing (SOP Header, SOP Line). Each record type would have to be defined as a separate file in the integration, and saved as text delimited. This process can become quite combersome, especially when dealing with Excel to text conversion rules (try saving an Excel field as CSV with leading zeros in the field data…). Add on top of that fact that these integrations are regularly re-occurring, and this process can quickly become stale.

To get around some of these problems, we can use ODBC. IM provides two types of ODBC connection; I prefer to use the Advanced OBDC version. Let’s walk trough an excercise.

1. Create an Excel file with two tabs – one for SOP Header and one for SOP Line information. Make sure the lines can at least be related to the header via document number. Save your Excel file to a network location. Name the tabs “Header” and “Line”.
2. Go to Control Panel->Administrative Tools->Data Sources (ODBC). Create an Excel ODBC data source (System DSN) that connects to the file location – name it “IM Test”. You will need to click “Select Workbook” and then browse to the network location and point the Data Source to the Excel File saved in step 1.

   

3. Start integration manager. Create a new integration called “Single File Test”.



4. Add a source to the integration. When prompted for the Source Type, select “Advanced ODBC”. Click on the “Define New Advanced ODBC” node. This will open the source query dialog.

   

5. Name your source query “SOPHDR”. In the data source drop down, select “IM Test”. This is the ODBC connection we set up in step 2. Paste the following statement into the SQL Statement box: SELECT * FROM [Header$]



6. Click “Apply”. Check out the columns tab – notice that IM was able to read the columns from the Header worksheet.
7. Repeats steps 4-6 for the Line tab in the Excel worksheet. Note that the SQL Statement will change to: SELECT * FROM [Line$]
8. Now, configure your integration as you normally would by adding an SOP destination, query relationships between SOPHDR and SOPLINE, and destination mapping.
9. Run the integration – we now are able to run an IM integration with multiple sources, all from the same Excel file.

Why is this useful? Well, a good Excel person could create a template or macro to manipulate the original source data into the correct integration format. They could save this template/macro somewhere to be re-used. Each time the integration needs to be run, the Excel template is opened, the source data is feed to it (copy and paste anyone?), and the manipulation is done via template/macro. We have not quite hit the value point: now just save the loaded template file to the ODBC network location…run the integration – all done! There is no more saving of multiple files to multiple locations with specific names. We have configured all that in our template (including tab names). Just one file, copied to one location.

I should not stop there; however, the text delimited IM approach we are used to using is actually just circumventing the Windows operating system ODBC setup. IM is actually using ODBC in all configurations. You could mimic the text delimited setup by using the operating system Data Sources/ODBC connection and IM Advanced ODBC setup.

We can make one more conclusion from this reasoning – you can use ANY ODBC connection for IM, including direct connections to SQL Server, Oracle, and Microsoft Access! Here is a sample of some of the ODBC connections provided by windows:

“All the world’s a stage, and all the men and women merely players: They have their exits and their entrances” – As You Like It by William Shakespeare

2 Responses to “Integration Manager and ODBC”