HPSCAT vendor data ingestion pipeline

Automated data pipeline that pulls vendor volume data from the HPSCAT API and uploads it to SharePoint via Microsoft Graph. It replaced a manual monthly process and adapts to a changing manufacturer roster.

What it pulls

Vendor volume data across the company's changing manufacturer portfolio.

Why PowerShell, not Power Automate

API access is issued per-consumer and bound to a fixed set of source addresses. Power Automate's egress IPs are dynamic, so they can't satisfy a fixed-source requirement reliably. Running the pipeline as PowerShell from a known, stable source solved it without asking the vendor to loosen anything.

Variants

• HPSCAT-SingleVendor One-vendor refresh, used for ad hoc pulls.
• HPSCAT-MultiVendor The standard monthly run across the configured manufacturer roster.
• HPSCAT-MultiDay Multi-day windows when a single day is short.
• HPSCAT-MultiVendor-Backfill Historical backfill across vendors and date ranges.

SharePoint integration

Uploads land in document libraries via Microsoft Graph with structured folder layout. Permissions and folder structure are managed automatically so downstream BI work doesn't need to chase access.

Operational shape

• Scheduled runs with structured logging; failure alerting is a known gap tracked for the next hardening pass.
• Idempotent retries: rerunning the same window produces the same result.
• Field mapping and validation to handle vendor-specific schema quirks.

Hardening pass: credentials and hosting

A fleet-wide audit of undocumented scheduled automation flagged this pipeline for two fixes, both completed as a reviewed set of pull requests: the API credential moved out of plain configuration into a DPAPI-encrypted file scoped to the service account, and the job moved off a shared utility server running under a named personal account onto a dedicated host under its own domain service account. Deployment is now git-based (clone once, pull to update), with a prerequisite checker that reports pass, warning, or an action item, and a self-cleaning write test that proves upload access rather than just checking that the stored credential decrypts.