SET NOCOUNT ON
DECLARE @folderName varchar(255),@projectName varchar(255);
DECLARE @validation_id bigint,@validation bigint
DECLARE @message NVARCHAR(MAX)
DECLARE @Cr as varchar(10)=char(13)
DECLARE @CrLf as varchar(10)=char(13)+Char(10)
DECLARE ssis_cursor CURSOR FOR
SELECT folderName , projectName as ProjectPath FROM
(
SELECT folders.name as folderName
, projects.name as projectName
,ROW_NUMBER() OVER (PARTITION BY projects.[project_id] ORDER BY last_deployed_time DESC) AS RowNum
FROM [SSISDB].[catalog].[projects] projects
inner join [SSISDB].[catalog].[folders] folders
on folders.folder_id = projects.folder_id
) AS A WHERE RowNum = 1
order by ProjectPath
OPEN ssis_cursor
FETCH NEXT FROM ssis_cursor
INTO @folderName, @projectName
WHILE @@FETCH_STATUS = 0
BEGIN
Print '---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
Print 'Validaton of :' + @projectName + '\' + @folderName
EXECUTE SSISDB.catalog.validate_project
@folder_name =@folderName,
@project_name = @projectName,
@validate_type = 'F',
@validation_id = @validation_id OUTPUT,
@use32bitruntime = 0,
@environment_scope = 'A'
set @validation= @validation_id
print 'ValidationID:' + cast(@validation as varchar(100))
WAITFOR DELAY '00:00:10';
SEt @message= 'SSIS Validation' +@Cr
Select @message = @message + message_desc + ' - ' + message + @Cr from SSISDB.catalog.operation_messages m
INNER JOIN
(
VALUES
(-1,'Unknown')
, (120,'Error')
, (110,'Warning')
, (70,'Information')
, (10,'Pre-validate')
, (20,'Post-validate')
, (30,'Pre-execute')
, (40,'Post-execute')
, (60,'Progress')
, (50,'StatusChange')
, (100,'QueryCancel')
, (130,'TaskFailed')
, (90,'Diagnostic')
, (200,'Custom')
, (140,'DiagnosticEx')
, (400,'NonDiagnostic')
, (80,'VariableValueChanged')
) D (message_type, message_desc)
ON D.message_type = m.message_type
where operation_id = @validation
Print @CrLf
select CASE status
-- created (1)
-- running (2)
-- canceled (3)
-- failed (4)
-- pending (5)
-- ended unexpectedly (6)
-- succeeded (7)
-- stopping (8)
-- completed (9).
WHEN 1 THEN cast(6 as char(1)) + ' created internal error'
WHEN 2 THEN cast(6 AS Char(1)) + ' running internal error'
WHEN 3 THEN cast(3 AS Char(1)) + ' cancelled'
WHEN 4 THEN cast(1 AS Char(1)) + ' failed'
WHEN 5 THEN cast(6 AS Char(1)) + ' Pending internal error '
WHEN 6 THEN cast(6 AS Char(1)) + ' ended internal error'
WHEN 7 THEn cast(0 AS Char(1)) + ' succeeded'
WHEN 8 THEN cast(1 AS Char(1)) + ' completed but failed'
END as Status,
@message as Message from SSISDB.catalog.validations
where validation_id = @validation
Print @CrLf
Print @message
Print @Crlf
Print @Crlf
Print @Crlf
FETCH NEXT FROM ssis_cursor
INTO @folderName, @projectName
END
CLOSE ssis_cursor;
DEALLOCATE ssis_cursor;
