| name | ssisdb-deployment |
| description | Use when deploying an .ispac to SSISDB and executing packages via catalog stored procedures (catalog.deploy_project, catalog.create_execution, catalog.start_execution). Covers folder/project/environment lifecycle, parameter overrides, and status polling. |
SSISDB deployment and execution
When to load this skill
- Running the
deploy-and-execute prompt (currently roadmap).
- Writing or reviewing
Publish-SsisIspac.ps1 / Start-SsisExecution.ps1 (both roadmap primitives).
- Debugging an execution status (failed deploy, package validation, execution timeout).
Prerequisites
- SQL Server with
SSISDB catalog provisioned (CLR enabled, catalog created via SSMS → Integration Services Catalogs → Create Catalog).
- The SQL login running the deploy is a member of
ssis_admin (full) or db_owner on SSISDB for the target folder.
- For execution: the login is a member of
ssis_admin or has been granted EXECUTE on the catalog procs.
Deploy procedure
$Server = '.\SQL2025'
$Folder = 'Demo'
$Project = 'CopilotSSISDemo'
$Ispac = 'out\CopilotSSISDemo.ispac'
# 1. Create folder if not present
Invoke-Sqlcmd -ServerInstance $Server -Database SSISDB -Query @"
IF NOT EXISTS (SELECT 1 FROM catalog.folders WHERE name = N'$Folder')
EXEC catalog.create_folder @folder_name = N'$Folder';
"@
# 2. Deploy via streamed varbinary
$bytes = [System.IO.File]::ReadAllBytes($Ispac)
$conn = New-Object System.Data.SqlClient.SqlConnection "Server=$Server;Database=SSISDB;Integrated Security=true;TrustServerCertificate=true"
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "DECLARE @op bigint; EXEC catalog.deploy_project @folder_name = @f, @project_name = @p, @project_stream = @s, @operation_id = @op OUTPUT; SELECT @op;"
$null = $cmd.Parameters.Add('@f', [System.Data.SqlDbType]::NVarChar, 128).Value = $Folder
$null = $cmd.Parameters.Add('@p', [System.Data.SqlDbType]::NVarChar, 128).Value = $Project
$null = $cmd.Parameters.Add('@s', [System.Data.SqlDbType]::VarBinary, -1).Value = $bytes
$operationId = $cmd.ExecuteScalar()
Then validate via catalog.validate_project (whole project) or catalog.validate_package (one package):
DECLARE @valid bigint;
EXEC SSISDB.catalog.validate_package
@folder_name = N'Demo',
@project_name = N'CopilotSSISDemo',
@package_name = N'Dim_Customer.dtsx',
@validation_id = @valid OUTPUT;
Validation results live in catalog.validations / catalog.operation_messages.
Execute procedure (three steps)
DECLARE @exec_id bigint;
EXEC SSISDB.catalog.create_execution
@folder_name = N'Demo',
@project_name = N'CopilotSSISDemo',
@package_name = N'Dim_Customer.dtsx',
@use32bitruntime = 0,
@reference_id = NULL,
@execution_id = @exec_id OUTPUT;
EXEC SSISDB.catalog.set_execution_parameter_value
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = N'RunDate',
@parameter_value = '2025-01-15';
EXEC SSISDB.catalog.start_execution @execution_id = @exec_id;
Then poll:
SELECT execution_id, status, start_time, end_time
FROM SSISDB.catalog.executions
WHERE execution_id = @exec_id;
status values:
| Value | Meaning |
|---|
| 1 | Created |
| 2 | Running |
| 3 | Cancelled |
| 4 | Failed |
| 5 | Pending |
| 7 | Succeeded |
| 8 | Stopping |
| 9 | Completed |
Messages and per-component statistics:
SELECT * FROM SSISDB.catalog.operation_messages WHERE operation_id = @exec_id;
SELECT * FROM SSISDB.catalog.execution_data_statistics WHERE execution_id = @exec_id;
Connection-manager parameter binding
To override a connection manager property at execution time, the @parameter_name follows the convention CM.<ConnMgrName>.<PropertyName>:
EXEC SSISDB.catalog.set_execution_parameter_value
@execution_id = @exec_id,
@object_type = 20,
@parameter_name = N'CM.Source.InitialCatalog',
@parameter_value = N'AdventureWorks2025';
Don't
- Don't use
dtexec /ISServer from the toolkit's helpers — the catalog procs are the supported automation path and surface SQL errors directly.
- Don't
EXEC catalog.deploy_project from a SQL Agent job with sensitive-data parameters baked in. Use environment references.
- Don't
EXEC catalog.create_execution with @use32bitruntime = 1 unless the package legitimately needs a 32-bit OLE DB provider (rare on modern SQL Server).
References
