如何使用PowerShell对SSIS服务器的SQL Job执行SQL查询或存储过程

首先你提到的仅通过$sqlJob = $ssisServer.JobServer.Jobs[$existing_job_name]获取Job的逻辑，无法直接关联到你指定的SSIS catalog folder、project路径下的资源：

• SQL Agent Job是SQL Server Agent服务下的独立对象，Job名称在单个SQL Server实例的Agent中唯一，上述代码只会按名称匹配返回对应的Job对象，不会校验Job内部关联的SSIS包路径
• 如果你需要确保获取的Job确实绑定了你指定目录下的SSIS项目包，需要额外遍历Job的步骤，校验步骤中配置的SSIS包路径参数

分两种常用场景给你参考：

场景1：操作现有SQL Agent Job（启动/停止/查状态/执行内置SQL逻辑） #

你已经通过SMO拿到了Job对象，直接调用Job的内置方法即可完成操作，不需要额外写SQL查询，除非你需要直接操作msdb库中Job的底层元数据。

场景2：无需依赖SQL Agent Job，直接调用SSIS目录下的存储过程/执行SSIS包 #

如果你的需求只是执行指定folder、project下的SSIS包或者调用SSIS catalog的内置存储过程，可以直接通过SMO调用执行方法，或者构造SQL语句调用msdb、SSISDB库的存储过程执行。

以下是你现有代码的补充完善版本，包含Job校验、常规操作、自定义SQL执行的完整逻辑：

# 初始化参数（请替换为实际值）
$name_of_server = "你的SSIS服务器实例名"
$catalogName = "SSIS目录名称，默认是SSISDB"
$folderName = "你指定的文件夹名"
$projectName = "你指定的项目名"
$existing_job_name = "目标SQL Agent Job名称"

# 原有服务连接逻辑
$ssisServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($name_of_server) 
$IntegrationServices = New-Object "Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices" $ssisServer
$catalog = $IntegrationServices.Catalogs[$catalogName]
$folder = $catalog.Folders[$folderName]    
$project = $folder.Projects[$projectName]
$sqlJob = $ssisServer.JobServer.Jobs[$existing_job_name]

if ($sqlJob) {
    # （可选）校验Job是否关联指定的SSIS项目包，避免取错Job
    $jobMatch = $false
    foreach ($step in $sqlJob.JobSteps) {
        # 仅筛选类型为SSIS包执行的步骤
        if ($step.SubSystem -eq 'SSIS') {
            # 匹配步骤配置的SSIS包路径是否和你指定的目录、文件夹、项目一致
            if ($step.Command -match "/ISSERVER \""\\$($catalog.Name)\$($folder.Name)\$($project.Name)"") {
                $jobMatch = $true
                break
            }
        }
    }
    if (-not $jobMatch) {
        Write-Error "当前获取的Job未关联指定SSIS目录下的项目，请检查Job名称配置"
        exit
    }

    # 1. 查看Job当前运行状态
    Write-Host "Job当前状态：" $sqlJob.CurrentRunStatus

    # 2. 启动Job执行
    $sqlJob.Start()

    # 3. 等待Job执行完成，获取执行结果
    do {
        Start-Sleep -Seconds 5
        $sqlJob.Refresh()
    } while ($sqlJob.CurrentRunStatus -ne 'Idle')
    Write-Host "Job执行最终结果：" $sqlJob.LastRunOutcome

    # 如果你需要执行自定义SQL查询操作Job的元数据，可以直接通过SMO的连接上下文执行
    $customQuery = "SELECT * FROM msdb.dbo.sysjobs WHERE name = N'$existing_job_name'"
    $queryResult = $ssisServer.ConnectionContext.ExecuteWithResults($customQuery)
    $queryResult.Tables[0] | Format-Table
}
else {
    Write-Error "未找到指定名称的SQL Agent Job"
}

补充说明 #

• 如果你不需要通过SQL Agent Job中转，直接执行指定SSIS项目下的包，可以直接调用$package.Execute($false, $null)方法，其中$package是你从$project.Packages[$packageName]获取到的包对象
• SSIS catalog的所有操作本质上都是调用SSISDB库下的存储过程，你也可以直接构造对应的SQL语句，通过你之前常用的PowerShell执行SQL查询的方式调用，常用的核心存储过程包括[catalog].[create_execution]（创建执行实例）、[catalog].[set_execution_parameter_value]（设置执行参数）、[catalog].[start_execution]（启动包执行）

内容的提问来源于stack exchange，提问作者HAH