Microsoft Dynamics GP and Google BigQuery integration

What moves between them

The flow runs from Microsoft Dynamics GP into Google BigQuery. ml-connector reads vendors, payables invoices, purchase orders, AP payments, and GL transactions from GP and loads each record type into its own BigQuery table, such as vendors, invoices, purchase_orders, payments, and gl_entries. The connector polls GP on a schedule, filtering by ModifiedDate where GP exposes it and by status or batch where it does not, then streams new and changed rows into BigQuery. The direction is one-way: BigQuery is treated as a read-only analytics destination, so ml-connector never writes financial entries back into GP.

How ml-connector handles it

ml-connector stores both credential sets encrypted. For GP it presents the Windows domain account over HTTP Negotiate or NTLM against the customer's SBA or SOAP endpoint, using the full server URL, tenant, and company database name they provide, since the company name is the SQL database name rather than the display name. For BigQuery it signs a JWT with the service account private key, exchanges it for a bearer token, and refreshes before the one-hour expiry; the literal newline characters in the private key PEM are preserved so signing does not break. Because neither side pushes events, the connector polls GP by modified date and writes to BigQuery through the streaming insertAll endpoint, attaching a stable insertId per record so a retried batch is deduplicated within BigQuery's best-effort window. GP returns validation errors rather than silent duplicates, and posted GP transactions are read-only, so the connector reads them as final. BigQuery schema growth is additive: new columns are added as nullable rather than renamed, since renames are destructive. GP has no published rate limit but its on-premises SQL Server can be strained, so the connector caps concurrency and can run in off-hours windows; BigQuery quotas are handled with backoff and idempotent job IDs on batch loads.

A real-world example

A regional building-materials distributor with about 300 employees runs Microsoft Dynamics GP on a hosted Windows server for payables, purchasing, and the general ledger. Their analysts needed spend and cash-flow reporting across vendors and locations, but every report meant a finance person exporting GP data to spreadsheets, and heavy queries against the live ERP slowed order entry for the branches. After connecting GP to Google BigQuery, vendor, invoice, purchase order, and GL records load into BigQuery tables on a nightly schedule, and the analysts build dashboards in SQL without touching the production ERP. Month-end reporting no longer waits on manual exports, and GP stays responsive during the day.

What you can do

• •Load Microsoft Dynamics GP vendors, payables invoices, purchase orders, payments, and GL transactions into Google BigQuery tables.
• •Poll GP on a schedule by modified date, status, or batch, since GP provides no webhooks or change events.
• •Bridge GP Windows Active Directory authentication and the BigQuery service account JWT-bearer token, refreshing before the one-hour expiry.
• •Stream rows into BigQuery with a stable insertId so retried batches do not create duplicate records.
• •Add new fields to BigQuery tables as nullable columns and keep concurrency low to protect the on-premises GP server.

Questions

Which direction does data move between Microsoft Dynamics GP and Google BigQuery?

Data moves one way, from GP into BigQuery. The connector reads vendors, invoices, purchase orders, payments, and GL transactions out of GP and loads them into BigQuery tables for SQL analysis. BigQuery is treated as a read-only reporting destination, so ml-connector never writes financial entries back into GP.

How does the integration authenticate to two systems with very different security models?

GP uses Windows Active Directory accounts with no API keys, so ml-connector presents a dedicated domain account over HTTP Negotiate or NTLM to the customer's SBA or SOAP endpoint. BigQuery uses a Google service account, so the connector signs a JWT with the service account private key and exchanges it for a one-hour bearer token. Both credential sets are stored encrypted, and the BigQuery token is refreshed before it expires.

How are new and changed records detected if neither system sends events?

Neither GP nor BigQuery pushes webhooks, so the connector polls. It queries GP for records changed since the last run using the ModifiedDate filter where GP exposes it, and falls back to status or batch filters where it does not. New and changed rows are then streamed into BigQuery with an insertId for best-effort deduplication, and the schedule can run in off-hours windows to avoid loading the on-premises GP server during business hours.