Commands / Test-sqmSSISPackageCompatibility
Cluster & Services

Test-sqmSSISPackageCompatibility

Validates SSIS packages against a target SQL Server version (2016–2025) before an upgrade — checking package version compatibility and deprecated/removed connection providers (SQLNCLI, ACE OLEDB). Reads from two independent sources: deployed packages in the SSISDB catalog and/or standalone .dtsx files on disk.

Module: sqmSQLTool
Requires: dbatools (SSISDB source)
ShouldProcess: No
Output: PSCustomObject

Execution Flow

START Neither -SqlInstance nor -PackagePath given? throw Map -TargetVersion → VersionMajor (2016=13 ... 2025=17) -SqlInstance given? PHASE 1 — SSISDB Connect (TrustServerCertificate for SQL 2022+), check SSISDB exists Query catalog.packages/projects/ folders (optional -FolderName filter) Check: pkg.version_major > target? → Error, else OK -PackagePath given? PHASE 2 — Filesystem Get-ChildItem *.dtsx (-Recurse) Parse XML, DTS namespace Check: VersionMajor > target? → Error, else OK Check: ConnectionManager strings SQLNCLI10/11 → Warning (use MSOLEDBSQL) ACE.OLEDB.12 → Error (removed SQL 2025) PHASE 3 — count OK / Warning / Error Overall = worst status present (Error > Warning > OK) Write TXT + CSV + dark-theme HTML report, open unless -NoOpen Return PSCustomObject (Status, Checks[], HtmlFile, TxtFile, CsvFile) DONE
Both sources can run in the same call — giving both -SqlInstance and -PackagePath lets you compare deployed production packages against an on-disk backup/staging copy in one report. On SQL Server 2022+, the SSISDB connection pre-emptively sets TrustServerCertificate to handle self-signed certificates that would otherwise break the initial connection. ACE.OLEDB.12.0 connections are flagged as an Error (not just a warning) specifically for a 2025 target, since that provider is removed outright in that version.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalSQL Server instance to connect to for the SSISDB source. Omit to check only filesystem packages.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-FolderNameString[]OptionalRestrict SSISDB packages to specific catalog folder(s).
-PackagePathStringOptionalPath to .dtsx files (filesystem source). Omit to check only SSISDB packages.
-RecurseSwitchOptionalRecurse into subfolders when reading .dtsx files.
-TargetVersionIntOptionalTarget SQL Server version: 2016, 2017, 2019, 2022, or 2025. Default: 2022.
-OutputPathStringOptionalDirectory for HTML/TXT/CSV reports. Default: $env:ProgramData\sqmSQLTool\SSISReports (or the module config OutputPath).
-NoOpenSwitchOptionalSuppress automatically opening the HTML report.
-EnableExceptionSwitchOptionalThrow exceptions instead of returning an error-status object.

Return Value

Returns a single PSCustomObject with: SqlInstance, PackagePath, TargetVersion, CheckCount, CountOk, CountWarn, CountErr, Status (OK, Warning, or Error), Checks (array of per-package/connection findings), HtmlFile, TxtFile, CsvFile, Message.

Examples

Example 1 — Check deployed packages on the target server

Test-sqmSSISPackageCompatibility -SqlInstance "NewServer2025" -TargetVersion 2025

Example 2 — Check package files before deployment

Test-sqmSSISPackageCompatibility -PackagePath "C:\OldPackages" -TargetVersion 2025 -Recurse

Example 3 — Compare deployed vs. backup packages in one report

Test-sqmSSISPackageCompatibility -SqlInstance "NewServer2025" -PackagePath "C:\OldPackages" -TargetVersion 2025
⬆ Index
All Commands