Skip to main content
Automation

VisualCron Job Export to SQL Server

PowerShell 5.1 import pipeline that reads configured VisualCron XML exports, stages normalized job metadata into VC.Jobs_Staging, and executes VC.SP_PROCESSJOBS for final processing.

Overview

Imports VisualCron job XML exports from a JSON file list, validates every path, parses ArrayOfJobClass.JobClass nodes, and loads job rows to SQL Server staging by source server.

Business Purpose

Provide repeatable and auditable operational job metadata ingestion so scheduling visibility, governance checks, and reporting can be processed from a centralized SQL dataset.

Technology Stack

PowerShell 5.1 (64-bit)SqlServer ModuleInvoke-SqlcmdPSLoggerSecretStoreSQL Server

Execution Model

Five-phase execution pattern: module bootstrap, file list validation, secret resolution, XML parse and staging load, then final stored procedure processing.

Pipeline Phases

  1. Local module bootstrap and logger setup from .\modules or ..\modules.
  2. vc-files.json validation with rooted path and file existence checks.
  3. SecretStore lookup for ConnectionStrings:{ConnectionStringName}.
  4. Per-file XML parse, DataTable mapping, and bulk load to VC.Jobs_Staging.
  5. Final execution of EXEC VC.SP_PROCESSJOBS.

Key Runtime Behaviors

  • ServerName is derived from the XML filename without extension.
  • InsertDateTime is stamped at import time and InsertProcess is set to POWERSHELL(GET_VC.ps1).
  • JobCreatedDate and JobModifiedDate values with year 1900 or earlier are written as database null.
  • Bulk copy only runs when at least one row exists for the current XML file.
  • Empty XML job sets generate warnings instead of failing the overall run.
  • DryRun mode still validates files, resolves secrets, parses XML, and runs final processing while skipping bulk copy writes.

Staged Columns and SQL Targets

JobID JobName JobStatus GroupName Description JobCreatedBy JobCreatedDate JobModifiedBy JobModifiedDate ServerName InsertDateTime InsertProcess

Primary target: VC.Jobs_Staging. Final processing command: EXEC VC.SP_PROCESSJOBS.

Execution Examples

.\VC.ps1 -ConnectionStringName ReportingSQL

.\VC.ps1 -ConnectionStringName ReportingSQL -DryRun

$pw = Read-Host 'Secret store master password' -AsSecureString
.\VC.ps1 -ConnectionStringName ReportingSQL -SecretFile C:\Secrets\vc.store -MasterPassword $pw