如何从SQL Server Agent作业命令提取SSIS项目参数值？

问题背景 #

我有一个名为MyPackage的包，属于MyProject项目，包含三个参数：ParameterA、ParameterB、ParameterC：

• ParameterA通过环境变量设置值
• ParameterB使用design_default_value（设计默认值）
• ParameterC在项目配置中默认值为'SomeValue'，但在执行包的SQL Server Agent作业中被手动设置为'OtherValue'

通过object_parameters和environment_variables视图能获取前两个参数的值，但现有查询无法提取作业中手动设置的ParameterC值，只能拿到完整的作业命令。示例作业命令如下：

/ISSERVER "\"\SSISDB\MyFolder\MyPackage.dtsx\"" /SERVER "\"MyServer\"" /ENVREFERENCE 13 /Par "\"$Project::ParameterC\"";"\"OtherValue\"" /Par "\"$Project::ParameterD\"";"\"ValueforParameterD\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E

需要提取/Par "\"$Project::到/CALLERINFO之间的项目参数，最终得到如下记录集：

解决方案 #

通过字符串截取+拆分函数结合CTE处理作业命令，提取目标参数和对应值，修改原查询中的参数处理逻辑如下：

完整查询代码 #

; WITH EnvironmentValues AS 
(
    SELECT 
        er.project_id,
        ev.name AS variable_name,
        CAST(ev.value AS nvarchar(max)) AS environment_value
    FROM 
        SSISDB.catalog.environment_references er
    LEFT JOIN 
        SSISDB.catalog.environment_references er_ref ON er.reference_id = er_ref.reference_id
    LEFT JOIN 
        SSISDB.catalog.environment_variables ev ON er_ref.environment_folder_name = ev.name
),
DesignAndDefaultValues AS 
(
    SELECT 
        op.project_id,
        op.object_name AS package_name,
        op.parameter_name,
        CAST(op.design_default_value AS nvarchar(max)) design_default_value,
        CAST(op.default_value AS nvarchar(max)) default_value,
        op.object_type
    FROM
        SSISDB.catalog.object_parameters op
    LEFT JOIN 
        SSISDB.catalog.projects p ON op.project_id = p.project_id
    WHERE 
        op.parameter_name NOT LIKE 'CM.%' 
        AND p.project_id = 27
),
JobStepParameters AS 
(
    -- 截取/CALLERINFO之前的命令部分，只保留包含$Project::的/Par参数段
    SELECT 
        js.job_id,
        js.step_id,
        j.name AS job_name,
        j.enabled as job_enabled,
        js.command AS package_command,
        SUBSTRING(js.command, 
                  CHARINDEX('/Par "\"$Project::', js.command),
                  CHARINDEX('/CALLERINFO', js.command) - CHARINDEX('/Par "\"$Project::', js.command)) AS project_params_raw
    FROM
        msdb.dbo.sysjobs j
    LEFT JOIN 
        msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
    WHERE
        js.subsystem = 'SSIS'
        AND js.command LIKE '%/Par "\"$Project::%'
),
-- 拆分参数段为单个参数行
SplitParams AS (
    SELECT
        job_id,
        step_id,
        job_name,
        job_enabled,
        package_command,
        TRIM(value) AS param_segment
    FROM JobStepParameters
    CROSS APPLY STRING_SPLIT(project_params_raw, '/Par')
    WHERE TRIM(value) <> ''
        AND value LIKE '&quot;\&quot;$Project::%'
),
-- 提取参数名和对应值
ExtractedParams AS (
    SELECT
        job_id,
        step_id,
        job_name,
        job_enabled,
        package_command,
        -- 提取参数名：$Project::后到第一个&quot;之间的内容
        SUBSTRING(param_segment, 
                  CHARINDEX('$Project::', param_segment) + 11,
                  CHARINDEX('\&quot;&quot;;', param_segment) - CHARINDEX('$Project::', param_segment) - 11) AS parameter_name,
        -- 提取参数值：;之后到最后一个&quot;之间的内容
        SUBSTRING(param_segment, 
                  CHARINDEX(';&quot;\&quot;', param_segment) + 8,
                  CHARINDEX('\&quot;&quot;', param_segment, CHARINDEX(';&quot;\&quot;', param_segment) + 8) - CHARINDEX(';&quot;\&quot;', param_segment) - 8) AS manually_set_value
    FROM SplitParams
)
INSERT INTO @Resultstable
    SELECT DISTINCT
        f.name AS SSIS_Folder,
        p.name AS SSIS_Project,
        d.package_name AS SSIS_Package_Name,
        d.parameter_name AS SSIS_Parameter_Name,
        ep.job_name AS SQL_Agent_Job_Name,
        ep.job_enabled,
        COALESCE(ep.manually_set_value, e.environment_value,
                 d.default_value, d.design_default_value) AS Parameter_Value_Used,
        CASE
            WHEN ep.manually_set_value IS NOT NULL 
                THEN 'Job manually set'
            WHEN e.environment_value IS NOT NULL 
                THEN 'Environment variable'
            WHEN d.default_value IS NOT NULL 
                THEN 'Default value in package'
            ELSE 'Design default value'
        END AS Value_Source
    FROM 
        SSISDB.catalog.folders f
    LEFT JOIN 
        SSISDB.catalog.projects p ON f.folder_id = p.folder_id
    LEFT JOIN 
        DesignAndDefaultValues d ON p.project_id = d.project_id
    LEFT JOIN 
        EnvironmentValues e ON p.project_id = e.project_id
                            AND d.parameter_name = e.variable_name
    LEFT JOIN 
        ExtractedParams ep ON ep.package_command LIKE '%' + d.package_name + '%'
                            AND d.parameter_name = ep.parameter_name
    ORDER BY
        1, 2, 3, 4, 5

关键逻辑说明 #

1. 截取目标参数段：通过SUBSTRING和CHARINDEX定位到第一个/Par "\"$Project::和/CALLERINFO之间的内容，过滤无关命令部分。
2. 拆分单个参数：使用STRING_SPLIT按/Par拆分截取后的内容，只保留项目级参数的行。
3. 提取参数名和值：再次通过SUBSTRING和CHARINDEX分别从每个参数段中分离出参数名和对应的值。

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