Export location, CSV outputs, resume behavior and payment-token checks.
Purpose and scope
Export-SiteWatchCustomers.ps1 exports customer, membership, payment-reference and prepaid reporting data for White Water. Confirm database compatibility with the migration team before use.
Its database commands are fixed SELECT queries against reporting procedures and views; it does not issue database INSERT, UPDATE or DELETE commands. It does write CSVs, checkpoints and a completion file to the local output directory.
The script has no site-filter parameter. Its scope follows the data accessible through the selected DSN and reporting procedures; it cannot export records that are absent from the connected database.
Where to run the export
For an all-site export, run the script on a server containing all site data, such as a QueryServer or the Replication Hub.
Running against a particular site's database exports only information replicated to that site. A successful run there can therefore produce a valid but incomplete export for a company-wide migration. The absence of a site-filter option does not make a site database an all-site source.
Before starting, confirm that the selected ODBC DSN points to the intended all-site database and that it contains the required sites' replicated data. The database reached by the DSN determines coverage; the workstation or server name alone does not establish it. Record the source server/database and intended site coverage with the run.
If moving from a site-scoped export to the central source, use a new output folder. Do not resume the site's saved checkpoints for an all-site run, even if the DSN has the same name: resume reuses completed query results and does not refresh them from the newly selected database.
Payment migration requires expert review
A completed export does not establish that its payment tokens are complete or usable for migration. The NXT Wash data team and the relevant processor subject matter experts must review the affected payment data before it is accepted for migration.
The NXT Wash data team reported that the CardConnect tokens they expected were usually longer values beginning with 9-. The new-member values in that reviewed export were shorter and did not resemble the expected format. The team requested the complete CardConnect token value to complete the migration.
This describes a concern with the reviewed data, not a universal CardConnect format specification. A matching prefix or length alone does not establish validity, and an unexpected format needs investigation rather than an assumed conversion.
- Have the data team and processor experts confirm the source field, complete original value, processor association and format required by the receiving migration process.
- Preserve the exported value exactly as text during review. Do not add
9-, pad, truncate or otherwise rewrite a value to make it resemble the expected format. - Keep affected payment records pending until the experts confirm their suitability. Resolve any missing or different-format values through the approved source or processor process.
The script copies the reporting view's payment-reference fields into the CSVs. It does not validate CardConnect token format or recover missing portions of a value. Its blank PROCESSOR field also leaves the processor mapping unresolved. Successful CSV creation and COMPLETE.txt confirm export completion, not payment-migration approval.
Requirements and options
The script requires PowerShell 5.1 or later, uses the built-in .NET ODBC classes and requires no additional PowerShell modules. The reporting ODBC driver and DSN must already work for the account running the export. The destination's parent folder must exist.
| Option | Behavior in this script |
|---|---|
-Dsn |
Defaults to SiteWatch. Selects the configured ODBC data source. |
-UserId |
Defaults to REPORTS. Supplies the reporting user name to the connection. |
-OutputDirectory |
Selects the run folder. Without it, a dated SiteWatchExport-... folder is created beside the script, or in the current filesystem folder if no script root is available. |
-AsOf |
Defaults to the current time. The script treats the supplied value as the database-local report cutoff. |
-CommandTimeoutSeconds |
Defaults to 600; accepts 1–86,400. Timeout enforcement depends on the ODBC driver. |
-Resume |
Reuses a saved run and requires an explicit -OutputDirectory. |
A new run rejects an existing output folder. Use a new folder for a new export or the resume option for the saved run.
Files produced
| File | Contents and counting rules |
|---|---|
ARM Data.csv |
Membership status, plan, recharge and customer details, with associated payment-reference fields and customer codes. The script stops if the query returns more than one row for the same customer. It does not merge family members sharing a payment ID. |
Token Data.csv |
Payment-reference columns projected from the ARM rows. Shared payment IDs can appear more than once; this is not a distinct-payment list. Values require the expert review described above before migration. |
ARM Analysis.csv |
Groups by location, plan and ARM status. PAYMENTCOUNT counts rows with a non-null payment ID, not distinct payment IDs. |
Club Data.csv |
Club-plan customer/code records with plan expiration strictly later than the cutoff. The script does not apply the ARM uniqueness check to this output. |
Prepaid Data.csv |
Prepaid records with a positive balance, including plan, site, code and date fields. The last-use value comes from the separate balance lookup. |
The PROCESSOR column is intentionally blank. The supplied script does not establish the processor mapping, so an empty value is not evidence of a particular processor.
Customer codes for ARM are retrieved in batches of 250 customer IDs and joined into a comma-separated field. CSV quoting preserves that field as one column. Files use UTF-8 with a byte-order mark, include a header even when no rows are returned, and format date and numeric values without depending on the workstation's display locale. Dates are written without a timezone suffix; retain the database-local cutoff with the export record.
Cutoff and historical scope
The ARM queries use Arm_Monthly_Current_Status and Arm_SalePass_Member. Club and prepaid balance queries use a starting date of January 1, 1980 and the selected cutoff. The prepaid last-use lookup reduces the time component to six-second increments, as implemented in the script.
The report cutoff does not make every joined customer, site or payment view a historical snapshot. The script performs separate queries and does not create a shared database snapshot transaction. A resumed run can therefore combine earlier saved results with newly queried data for unfinished stages.
Resume an interrupted run
The run folder contains a .checkpoints directory with completed query results and run.json. Completed ARM, customer-code batches, club and prepaid query results can be reused. An interrupted .tmp file is not treated as a completed query checkpoint.
The documented resume syntax, for an authorized operator, is:
.\Export-SiteWatchCustomers.ps1 -OutputDirectory 'C:\Exports\SiteWatchRun' -Resume
Replace the example folder with the actual saved run folder.
Resume checks the saved format and query fingerprint. It restores the saved DSN, reporting user and cutoff. If those options are supplied explicitly with different values, or if the query definition has changed incompatibly, the script stops. Start a new run folder for a fresh export or changed report scope. A run-folder lock prevents two script processes from using the same run simultaneously.
Completion and handling
Each CSV is first written to a temporary file and then moved into place or replaces the previous CSV for that run. A successful finish writes COMPLETE.txt after all five exports. Failed runs can leave earlier CSVs, and resume does not clear an older completion marker at startup, so confirm the current run's successful completion message as well as the expected files.
The output includes customer contact and vehicle information, notes and payment-related fields. Keep run folders and checkpoints in the approved restricted migration workspace. Do not attach actual exports or customer examples to this documentation library.
Database compatibility
The script targets 2020 reporting definitions. Confirm the installed database procedures, report scope and assumptions with the migration team before accepting an export.