I have a package called MyPackage. It is part of the project MyProject.
It has three parameters:
ParameterA, ParameterB, ParameterC
ParameterA is set through an environmentParameterB is using the design_default_valueI can find the values for these parameters using the catalog views object_parameters and environment_variables.
ParameterC though is more complex.
In the project configuration, ParameterC has the default value manually set to 'SomeValue'. I can find that through object_parameters as well.
In the SQL Server Agent Job running the package, the value of ParameterC is set to 'Othervalue'
And there is my question: where do I find the value 'Othervalue'?
I have written the query below but it does not give met 'Othervalue', it gives me the command executing the package. Not the value set for ParameterC which is 'Othervalue' in the SQL Server Agent Job configuration.
; 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 op.object_type = 20
AND p.project_id = 27
),
JobStepParameters AS
(
-- Assuming that you have a way to map job step parameters manually set in the SQL Agent job
SELECT
js.job_id,
js.step_id,
j.name AS job_name,
j.enabled as job_enabled,
js.command AS package_command,
-- Extract the relevant SSIS package and parameter values from the job step command
-- This part is tricky, as it depends on how the parameters are passed to the SSIS package
-- Adjust the extraction logic as needed
-- Example placeholder:
js.command AS manually_set_value
FROM
msdb.dbo.sysjobs j
LEFT JOIN
msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE
js.subsystem = 'SSIS'
)
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,
jp.job_name AS SQL_Agent_Job_Name,
jp.job_enabled,
COALESCE(jp.manually_set_value, e.environment_value,
d.default_value, d.design_default_value) AS Parameter_Value_Used,
CASE
WHEN jp.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
JobStepParameters jp ON jp.package_command LIKE '%' + d.package_name + '%'
ORDER BY
1, 2, 3, 4, 5
Edit: these values can be found in the js.command mentioned here above.
It looks like this:
/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
I need to extract the part between the first /Par "\"$Project:: and /CALLERINFO.
Then I need to decompose it as follows:
/Par "\"$Project::ParameterC\"";"\"OtherValue\""
/Par "\"$Project::ParameterD\"";"\"ValueforParameterD\""
/Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2
/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True
Keep only the pars that contain the text '/Par "\"$Project:::':
/Par "\"$Project::ParameterC\"";"\"OtherValue\""
/Par "\"$Project::ParameterD\"";"\"ValueforParameterD\""
Decompose these further so that you have these results as a recordset
parameter | value
-----------+--------------------
ParameterC | OtherValue
ParameterD | ValueforParameterD
How do I extract the parameters and their values from the js.command?
I needed two functions to get this working:
CREATE OR ALTER function [ssis].[split_command_text]
(
@command NVARCHAR(MAX)
)
RETURNS @Resultstable TABLE
( [command] nvarchar(max),
parameter_name nvarchar(100),
[value] nvarchar(max)
)
AS
BEGIN
IF @command like '%/Par "\"$Project::%'
BEGIN
;WITH NumberedParams AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
value AS param_pair
FROM STRING_SPLIT(REPLACE(@command, '/Par ', '|'), '|')
WHERE value LIKE '%$Project::%'
),
ExtractedParams AS (
SELECT
rn,
SUBSTRING(param_pair,
CHARINDEX('$Project::', param_pair) + 10,
CHARINDEX('""', param_pair, CHARINDEX('$Project::', param_pair) + 10) - (CHARINDEX('$Project::', param_pair) + 10)
) AS parameter,
CASE
WHEN CHARINDEX('";', param_pair) > 0 THEN
SUBSTRING(param_pair,
CHARINDEX('";', param_pair) + 2,
CASE
WHEN CHARINDEX(' /', param_pair, CHARINDEX('";', param_pair)) > 0
THEN CHARINDEX(' /', param_pair, CHARINDEX('";', param_pair)) - (CHARINDEX('";', param_pair) + 2)
ELSE LEN(param_pair) - (CHARINDEX('";', param_pair) + 1)
END
)
ELSE NULL
END AS value
FROM NumberedParams
)
insert into @Resultstable
SELECT
@command as command,
REPLACE(parameter, '\', '') AS parameter,
COALESCE(NULLIF(REPLACE(REPLACE(REPLACE(value, '"', ''), '\', ''), ';', ''), ''), 'NULL') AS value
FROM ExtractedParams
WHERE parameter NOT LIKE '%ServerOption%'
ORDER BY rn;
END
RETURN
END;
and
create or alter function ssis.get_actual_used_values(
@SSIS_Folder as nvarchar(255) = NULL,
@SSIS_Project as nvarchar(255) = NULL,
@SSIS_Parameter_Name as nvarchar(255) = NULL,
@Environment_name as nvarchar(255) = NULL,
@SQL_Agent_Job_Name as nvarchar(255) = NULL
)
RETURNS TABLE AS RETURN
(
WITH EnvironmentValues AS (
SELECT er.project_id,
ev.name AS variable_name,
cast(ev.value as nvarchar(max)) AS environment_value,
e.name as environment_name,
e.environment_id,
er.reference_id,
er.environment_folder_name
FROM SSISDB.catalog.environment_variables ev
LEFT JOIN SSISDB.catalog.environments e on ev.environment_id = e.environment_id
LEFT JOIN SSISDB.catalog.environment_references er on er.environment_name = e.name
LEFT JOIN SSISDB.catalog.projects pr on er.project_id = pr.project_id
LEFT JOIN SSISDB.catalog.folders f1 on e.folder_id = f1.folder_id
LEFT JOIN SSISDB.catalog.folders f2 on pr.folder_id = f2.folder_id
WHERE 1=1
AND f1.name = f2.name
AND (@Environment_name is null or e.name = @Environment_name)
AND (@SSIS_Project is null or pr.name = @SSIS_Project)
),
DesignAndDefaultValues AS (
SELECT distinct op.project_id,
p.name collate SQL_Latin1_General_CP1_CI_AS AS SSIS_project_name,
op.parameter_name collate SQL_Latin1_General_CP1_CI_AS as parameter_name,
cast(op.design_default_value as nvarchar(max)) collate SQL_Latin1_General_CP1_CI_AS as design_default_value,
cast(op.default_value as nvarchar(max)) collate SQL_Latin1_General_CP1_CI_AS as default_value
FROM SSISDB.catalog.object_parameters op
LEFT JOIN SSISDB.catalog.projects p ON op.project_id = p.project_id
WHERE op.parameter_name collate SQL_Latin1_General_CP1_CI_AS not like 'CM.%' and op.object_type = 20 --<-- 20 = project[parameter, 30 = package parameter
AND (@SSIS_Parameter_Name is null or op.parameter_name = @SSIS_Parameter_Name)
AND (@SSIS_Project is null or p.name = @SSIS_Project)
--and op.project_id = 231
--order by 2,3,4,5
),
JobStepParameters AS (
-- Assuming that you have a way to map job step parameters manually set in the SQL Agent job
SELECT
js.command as package_command
,dp.parameter_name
,dp.[value] as manually_set_value
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
OUTER APPLY ssis.split_command_text(js.command) as dp
WHERE js.subsystem = 'SSIS' and js.command = dp.command
AND (@SSIS_Parameter_Name is null or dp.parameter_name = @SSIS_Parameter_Name)
AND (@SQL_Agent_Job_Name is null or j.name = @SQL_Agent_Job_Name)
--order by 1,2,3
)
SELECT distinct f.name AS SSIS_Folder,
p.name AS SSIS_Project,
d.parameter_name AS SSIS_Parameter_Name,
COALESCE(
jp.manually_set_value,
d.default_value,
env_val.environment_value,
d.design_default_value
) AS Actual_Parameter_Value_Used,
CASE
WHEN jp.manually_set_value IS NOT NULL THEN 'Value manually set in SQL Agent Job'
WHEN d.default_value IS NOT NULL THEN 'Value manually set in project configuration'
WHEN env_val.environment_value IS NOT NULL THEN 'Takes value from environment'
WHEN d.design_default_value IS NOT NULL THEN 'Parameter not configured so value from package'
ELSE '<Cannot be determined>'
END AS why_this_value,
j.name AS SQL_Agent_Job_Name,
j.enabled as SQL_Agent_enabled,
-- d.package_name,
isnull(env_val.environment_folder_name,f.name) + '\' + env_val.environment_name as related_environment,
js.command
--, isnull(env_val.environment_folder_name, f.name ) as env_name
--,p.name as pname
--,cast(env_val.reference_id as varchar(10)) as envrefer
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 env_val ON p.project_id = env_val.project_id and d.parameter_name = env_val.variable_name
LEFT JOIN msdb.dbo.sysjobsteps js ON 1=1
--AND js.command collate SQL_Latin1_General_CP1_CI_AS LIKE '%' + isnull(env_val.environment_folder_name, f.name ) + '%' collate SQL_Latin1_General_CP1_CI_AS
AND js.command collate SQL_Latin1_General_CP1_CI_AS LIKE '%' + p.name + '%' collate SQL_Latin1_General_CP1_CI_AS
AND js.command collate SQL_Latin1_General_CP1_CI_AS LIKE '%/ENVREFERENCE ' + cast(env_val.reference_id as varchar(10))+ '%' collate SQL_Latin1_General_CP1_CI_AS
LEFT JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
LEFT JOIN JobStepParameters jp ON jp.package_command collate SQL_Latin1_General_CP1_CI_AS = js.command collate SQL_Latin1_General_CP1_CI_AS
and d.parameter_name = jp.parameter_name
and env_val.variable_name collate SQL_Latin1_General_CP1_CI_AS = jp.parameter_name collate SQL_Latin1_General_CP1_CI_AS
WHERE 1=1
and (@SSIS_Folder is null or f.name = @SSIS_Folder)
and (@Environment_name is null or env_val.environment_name = @Environment_name)
and (@SSIS_Parameter_Name is null or d.parameter_name = @SSIS_Parameter_Name)
and (@SSIS_Project is null or p.name = @SSIS_Project)
and (@SQL_Agent_Job_Name is null or j.name = @SQL_Agent_Job_Name)
);
Calling
select * from ssis.get_actual_used_values(NULL,NULL,NULL,NULL,NULL) order by 1,2,3,4,5,6,7,8,9
will tell you everything you'll want to know about your project-parameters. And yes, I know the code is ugly, be my guest to tidy it up and improve on it :)