Skip to main content

DonorPerfect Connector (Technical Reference)

Written by Philippe Trussart

Generated from connector source on 2025-09-04

Overview

The DonorPerfect connector pulls data from the DonorPerfect API into your Bytespree data lake.

Key characteristics:

  • API base: https://www.donorperfect.net/prod/

  • Endpoint used: xmlrequest.asp with HTTP GET parameters apikey and action.

  • Authentication: API key.

  • Discovery: table list is predetermined (see below) and validated against the tenant; tables with no rows are skipped.

  • Schema: dynamic per table (derived by sampling SELECT TOP 1 * FROM <table>); field names are lowercased when emitted.

  • Uniqueness: per-table keys are derived from columns ending in _id (plus dmi_id as a default, if present). dpflags uses a composite key.

Setup & Configuration

Provide the following setting:

{ "api-key": "<your DonorPerfect API key>" }

Sync Behavior

  • Control column (per table):

    • Uses a designated numeric id column to page through results (see Tables & Sync Keys). If a table is not listed in the mapping, id is used.

  • Initial position: computed as (MIN(id) - 1) for the control column; if unavailable, starts at -2147483648.

  • Batch size: TOP 500 rows per request.

  • Ordering: ascending by the control column (and by flag for dpflags).

  • Looping: continues fetching until no more rows are returned; progress is estimated via COUNT(*).

  • Special case (dpflags): rows are ordered by donor_id, flag; for stable pagination the connector tracks the last flag seen for the current donor_id.

  • State/bookmarks: persistent bookmarks are not written; each run iterates from the initial position up to the current end.

Error Handling & Retries

  • Retries up to 100 attempts with a 15s sleep between attempts if the response is empty or an error occurs.

  • Clear, user-facing errors for invalid credentials (401), missing endpoint (404), and generic server errors (500).

Schema Handling

  • Columns are discovered dynamically from the API.

  • Emitted field names are lowercased.

  • All fields are emitted as strings.

  • When building queries, any column whose name is a SQL Server reserved word is bracket-escaped (e.g., user → [user]).

Known Limitations / Notes

  • No persistent incremental bookmark; very large tables will require a full scan each run (though paging starts from the minimum id).

  • The API is queried with SQL-like statements; very wide tables or tables with many reserved-word columns may slightly slow discovery.

  • Composite key on dpflags (donor_id + flag) requires ordering by both fields; mixed-case or aliased field names are normalized to lowercase.

API Request Shapes (for reference)

Record count:

GET /prod/xmlrequest.asp?apikey=<APIKEY>&action=Select%20count(*)%20from%20<TABLE>

Initial id:

GET /prod/xmlrequest.asp?apikey=<APIKEY>&action=Select%20min(<ID>)%20as%20min_id%20from%20<TABLE>

Page fetch (typical):

GET /prod/xmlrequest.asp?apikey=<APIKEY>&action=SELECT%20TOP%20500%20<COLUMNS>%20FROM%20<TABLE>%20WHERE%20<ID>%20%3E%20<LAST_ID>%20ORDER%20BY%20<ID>%20ASC

Page fetch (dpflags):

GET /prod/xmlrequest.asp?apikey=<APIKEY>&action=SELECT%20TOP%20500%20<COLUMNS>%20FROM%20dpflags%20WHERE%20((donor_id%20%3E=%20<LAST_ID>%20AND%20flag%20%3E%20%27<LAST_FLAG>%27)%20OR%20donor_id%20%3E%20<LAST_ID>)%20ORDER%20BY%20donor_id,%20flag

Field-Level Notes

  • Expect common id columns (e.g., donor_id, gift_id, contact_id, address_id) to appear frequently.

  • For UDF-related tables (*udf, dpusermultivalues), column sets vary widely per tenant. Names are emitted exactly as provided by DonorPerfect (lowercased).

Did this answer your question?