Skip to main content

Connect to Dynamics GP with the Jet Data Manager

Overview

The Dynamics GP adapter simplifies the extraction of data from Microsoft Dynamics GP.

If you connect to a Dynamics GP database as a regular data source, you will have to apply and maintain selection rules on all tables because different companies are stored in separate databases.  With the Jet Data Manager Dynamics GP Adapter you can select company accounts at a global level and apply only one set of selection rules.  It is, however, also possible to overrule this behavior on a table by table basis.

Adding the Dynamics GP Adapater

  1. In the Solution Explorer, open the preferred business unit.

  2. Right-click Data Sources point to Adapter Data Sources , and then select Add Dynamics GP AdapterThe Dynamics GP Adapter Dialog will open. The Add Dynamics GP Adapter window appears.

  3. In the Name field assign a name. In this example we used GP.

  4. In the Server name field, enter the location of the server that Dynamics GP resides on.

  5. In the Database field , enter the name of the database (this should be the DYNAMICS database).

  6. Specify the authentication mode. The default setting is Windows authentication .  If you choose SQL Server authentication , you are prompted for a user name and a password.

  7. In the Command Timeout field , specify the number of seconds to wait before terminating the attempt to connect to the database.  The recommended value is 0 to disable the timeout. In the Connection Timeout field , specify the number of seconds to wait before terminating the attempt to connect to the server.

  8. If you want to add additional connection strings, click the Additional Connection Properties button.  In the Connection String Properties window type the preferred connection strings, and then click OK.

  9. The next window will be the GP Company Table Setup .  All settings should be left as default.  Click OK

    mceclip3.png

Setting Up the Dynamics GP Companies

When you have added a Dynamics GP adapter and specified a provider, you then have to set up the accounts which represent the companies for which data will be extracted from the data source.

  1. Right-click the adapter, and then choose Read Dynamics GP Companies . A dialog is displayed that shows all companies in the database.

    mceclip4.png

  2. In the template list, select the company account you want to use as template for the table and column structure. If you are only selecting one company then the template company must match the company that is selected.

  3. Select Use to specify whether to retrieve data from the company.

    In this example we used the Fabrikam, Inc company and the associated template.

    mceclip5.png

Loading and Selecting Data from your Dynamics GP Data Sources

  1. Right-click the Dynamics GP Adapter from which you want to select data, and then select Read Objects from Data SourceThe Data Selection pane on the right displays all tables, fields, and views.

  2. In the Data Selection pane, select the tables, fields, and views you want to extract to your staging database.

  3. There are two ways of viewing the data: Alphabetical view, which displays all tables alphabetically, and Group view where you specify how many tables each group must contain.

    To view data in groups, enter the number of tables in each group in the Group view field, and then click Group view. You can then group the tables alphabetically or by specifying the number of tables you want in each group. To view data alphabetically, click Alphabetical view.

The tables and fields are displayed in the data source tree and in the staging database tree.

Was this article helpful?

We're sorry to hear that.