SSIS_2012_Show_Connection_Strings

gistfile1.sql

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 = job_based_values.connection_string
			 THEN 'SAME'
			 WHEN project_based_values.Connection_String = job_based_values.connection_string
			 THEN 'DIFFERENT'
			 WHEN project_based_values.Connection_String IS NULL
			 THEN 'PROJECT'
			 WHEN job_based_values.connection_string IS NULL THEN 'JOB'
		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