Skip to content

Instantly share code, notes, and snippets.

@matt40k
Forked from mbourgon/gist:6029690
Last active November 11, 2018 18:42
Show Gist options
  • Save matt40k/0359b3d46487831311f62dc71e9f7905 to your computer and use it in GitHub Desktop.
Save matt40k/0359b3d46487831311f62dc71e9f7905 to your computer and use it in GitHub Desktop.
SSIS_2012_Show_Connection_Strings
USE ssisdb
--thebakingdba.blogspot.com 2013/07/18 1.00
--purpose - grab the connection strings from SSISDB for the package and the job runs.
SELECT ISNULL(project_based_values.project_name,
job_based_values.project_name) AS project_name,
ISNULL(project_based_values.package_name,
job_based_values.package_name) AS package_name,
ISNULL(project_based_values.parameter_name,
job_based_values.parameter_name) AS parameter_name,
project_based_values.Connection_String AS Project_Connection_String,
job_based_values.connection_string AS Job_Connection_String,
CASE
WHEN project_based_values.Connection_String IS NULL THEN 'PROJECT'
WHEN job_based_values.connection_string IS NULL THEN 'JOB'
WHEN project_based_values.Connection_String = job_based_values.connection_string THEN 'SAME'
WHEN project_based_values.Connection_String <> job_based_values.connection_string THEN 'DIFFERENT'
END AS Values_From
FROM (
SELECT ip.name AS project_name, [a].[object_name] AS package_name,
[a].[parameter_name],
[design_default_value] AS Connection_String
FROM (
SELECT [object_name], [parameter_name],
MAX([project_version_lsn]) AS max_project_version_lsn
FROM [internal].[object_parameters]
WHERE [parameter_name] LIKE '%connectionstring%'
GROUP BY [object_name], [parameter_name]
) a
INNER JOIN [internal].[object_parameters] a2
ON [a].[object_name] = [a2].[object_name]
AND [a].[parameter_name] = [a2].[parameter_name]
AND a.[max_project_version_lsn] = [a2].[project_version_lsn]
INNER JOIN internal.projects ip
ON a2.project_id = ip.project_id
) project_based_values
FULL OUTER JOIN (
SELECT project_name, package_name, parameter_name,
parameter_value AS connection_string
FROM internal.[execution_parameter_values]
INNER JOIN internal.executions
ON internal.execution_parameter_values.execution_id = internal.executions.execution_id
INNER JOIN (
SELECT MAX(execution_id) AS max_execution_id
FROM internal.executions
GROUP BY project_name, package_name
) most_recent_run
ON internal.executions.execution_id = most_recent_run.max_execution_id
WHERE parameter_name LIKE '%.connectionstring'
) job_based_values
ON project_based_values.project_name = job_based_values.project_name
AND REPLACE(project_based_values.package_name, '.dtsx', '') = REPLACE(job_based_values.package_name,
'.dtsx', '')
AND project_based_values.parameter_name = job_based_values.parameter_name
AND project_based_values.Connection_String = job_based_values.connection_string
ORDER BY 1, 2, 3, 6
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment