USE DBA
GO

CREATE TABLE #tmp_operation_id (
	id INT identity(1, 1) NOT NULL
	,operation_id BIGINT NULL
	,start_time DATETIME NULL
	,object_name_etl NVARCHAR(500) NULL
	,caller_name NVARCHAR(256) NULL
	,process_id BIGINT NULL
	,machine_name SYSNAME NULL
	)
/*
-- oryginalnie
INSERT INTO #tmp_operation_id
select operation_id, start_time, object_name as object_name_etl,caller_name,process_id, machine_name
from ssisdb.catalog.operations
where start_time IS NOT NULL 
and end_time IS NULL
order by created_time asc
 -- dla danych
 select *
into dba.dbo.Darek_operations
from ssisdb.catalog.operations
where operation_id=1085308

select *
into dba.dbo.Darek_event_messages
FROM [SSISDB].[catalog].[event_messages] msg 
WHERE msg.[operation_id] = 1085308
 AND [message_type] > 110

*/
--demo

INSERT INTO #tmp_operation_id
select operation_id, start_time, object_name as object_name_etl,caller_name,process_id, machine_name
from dba.dbo.Darek_operations
order by created_time asc

select * from #tmp_operation_id

declare @i int = 0, @j int = 1, @ExecutionID int, @tableHTML  NVARCHAR(MAX) = '', @k int = 0, @l int = 1, @bit int = 0
select @i=count(*)+1 from #tmp_operation_id

WHILE @i > @j
	BEGIN 
	select @ExecutionID = operation_id from #tmp_operation_id
	where id = @j

	;WITH msgEx AS(
		SELECT
			msg.[event_message_id]
			,msg.[operation_id]
			,CONVERT(datetime, msg.[message_time]) AS message_time
			,msg.[message_type]
			,msg.[message_source_type]
			,CASE WHEN LEN(msg.[message]) <= 4096 THEN msg.[message] ELSE LEFT(msg.[message], 1024) + '...' END AS [message]
			,msg.[extended_info_id]
			,msg.[event_name]
			,CASE WHEN LEN(msg.[message_source_name]) <= 1024 THEN msg.[message_source_name] ELSE LEFT(msg.[message_source_name], 1024) + '...' END AS [message_source_name]
			,msg.[message_source_id]
			,CASE WHEN LEN(msg.[subcomponent_name]) <= 1024 THEN msg.[subcomponent_name] ELSE LEFT(msg.[subcomponent_name], 1024) + '...' END AS [subcomponent_name]
			,CASE WHEN LEN(msg.[package_path]) <= 1024 THEN msg.[package_path] ELSE LEFT(msg.[package_path], 1024) + '...' END AS [package_path]
			,CASE WHEN LEN(msg.[execution_path]) <= 1024 THEN msg.[execution_path] ELSE LEFT(msg.[execution_path], 1024) + '...' END AS [execution_path]
			,msg.[message_code]
		--FROM [SSISDB].[catalog].[event_messages] msg 
		FROM dba.dbo.Darek_event_messages msg
		WHERE msg.[operation_id] = @ExecutionID
		 AND [message_type] > 110
		)
	SELECT 
		operation_id, 
		message_time, 
		message_type,
		[message],
		event_name, 
		message_source_name, 
		subcomponent_name, 
		package_path, 
		execution_path, 
		message_code		
	FROM msgEx
	ORDER BY [message_time] DESC
	set @ExecutionID = 0
	set @j = @j + 1
END
drop table #tmp_operation_id