Alle SSIS Projekte / Pakete validieren

Das Skript validiert alle im SSISDB-Katalog vorhandenen SSIS-Projekte und gibt für jedes Projekt einen detaillierten Bericht aus.

Ablauf im Einzelnen:

1. Ermittlung aller Projekte
   Die Tabelle catalog.projects wird mit catalog.folders verknüpft, um Ordner- und Projektnamen zu erhalten. Jedes Projekt wird genau einmal verarbeitet (die aktuellste Version, da SSISDB nur eine Zeile pro logischem Projekt speichert).

2. Schleife über alle Projekte
   Statt eines Cursors wird eine WHILE-Schleife mit einer temporären Tabelle verwendet – einfacher und wartbarer.

3. Starten der asynchronen Validierung
   Für jedes Projekt wird die gespeicherte Prozedur catalog.validate_project aufgerufen mit:

   • @validate_type = 'F' → vollständige Validierung aller Pakete im Projekt

   • @environment_scope = 'A' → alle Umgebungsverweise werden berücksichtigt

   • Die zurückgegebene @validation_id wird gespeichert.

4. Dynamisches Warten auf Abschluss
   Das Skript prüft jede Sekunde den Status der Validierung (Spalte status in catalog.validations). Es bricht ab, sobald der Status 3 (abgebrochen), 4 (fehlgeschlagen) oder 7 (erfolgreich) ist. Eine maximale Wartezeit von 5 Minuten verhindert Endlosschleifen.

5. Sammeln der Meldungen
   Aus catalog.operation_messages werden alle Meldungen zur Validierungs-Operation ausgelesen. Dabei werden die Nachrichten chronologisch (ORDER BY message_time) zu einem einzigen Text zusammengefügt. Fehler- und Warnmeldungen werden explizit gekennzeichnet.

6. Ausgabe des Ergebnisses
   Pro Projekt wird eine Ergebnismenge (SELECT) zurückgegeben mit:

   • Ordner- und Projektname

   • Validierungs-ID

   • Klartext-Status (z. B. succeeded, failed, canceled)

   • Alle gesammelten Meldungen als ein String

7. Fehlerbehandlung
   Tritt bei einem Projekt ein Fehler auf (z. B. ungültiger Projektname, fehlende Berechtigung), wird dieser abgefangen und in der Ergebnismenge als eigener Eintrag mit Fehlertext ausgegeben. Die Schleife läuft dann mit dem nächsten Projekt weiter.

SET NOCOUNT ON;

DECLARE @folderName VARCHAR(255),
        @projectName VARCHAR(255),
        @validation_id BIGINT,
        @status INT,
        @message NVARCHAR(MAX),
        @CrLf VARCHAR(10) = CHAR(13) + CHAR(10),
        @waitSeconds INT = 1,
        @maxWaitSeconds INT = 300,      -- Max. 5 Minuten pro Projekt
        @elapsedSeconds INT = 0;

-- Tabelle mit allen aktuellen Projekten (neueste Version)
DECLARE @projects TABLE (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    FolderName VARCHAR(255),
    ProjectName VARCHAR(255)
);

INSERT INTO @projects (FolderName, ProjectName)
SELECT 
    f.name,
    p.name
FROM SSISDB.catalog.projects p
INNER JOIN SSISDB.catalog.folders f ON p.folder_id = f.folder_id;
-- Hinweis: Es wird immer die aktuellste Version verwendet,
-- da SSISDB nur eine Zeile pro Projekt speichert.

DECLARE @maxId INT = (SELECT MAX(Id) FROM @projects),
        @currentId INT = 1;

WHILE @currentId <= @maxId
BEGIN
    SELECT 
        @folderName = FolderName,
        @projectName = ProjectName
    FROM @projects
    WHERE Id = @currentId;

    PRINT REPLICATE('-', 120);
    PRINT 'Validierung von: ' + @projectName + ' (Ordner: ' + @folderName + ')';

    BEGIN TRY
        -- Validierung starten (asynchron)
        EXECUTE SSISDB.catalog.validate_project
            @folder_name = @folderName,
            @project_name = @projectName,
            @validate_type = 'F',        -- Vollständige Validierung
            @validation_id = @validation_id OUTPUT,
            @use32bitruntime = 0,
            @environment_scope = 'A';    -- Alle Umgebungen

        PRINT 'Validierungs-ID: ' + CAST(@validation_id AS VARCHAR(20));

        -- Auf Abschluss der Validierung warten (max. @maxWaitSeconds)
        SET @elapsedSeconds = 0;
        WHILE @elapsedSeconds < @maxWaitSeconds
        BEGIN
            WAITFOR DELAY '00:00:01';
            SET @elapsedSeconds = @elapsedSeconds + 1;

            SELECT @status = status
            FROM SSISDB.catalog.validations
            WHERE validation_id = @validation_id;

            -- Status 7 = erfolgreich, 4 = fehlgeschlagen, 3 = abgebrochen, andere = läuft noch
            IF @status IN (3, 4, 7) BREAK;
        END

        -- Meldungen sammeln (chronologisch sortiert)
        SET @message = '';
        SELECT @message = @message + 
            CASE 
                WHEN D.message_desc = 'Error' THEN 'FEHLER: '
                WHEN D.message_desc = 'Warning' THEN 'WARNUNG: '
                ELSE ''
            END + 
            D.message_desc + ' - ' + ISNULL(m.message, '') + @CrLf
        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 m.operation_id = @validation_id
        ORDER BY m.message_time ASC;   -- Wichtig für korrekte Reihenfolge

        -- Ergebnis als Ergebnismenge ausgeben
        SELECT 
            @folderName AS FolderName,
            @projectName AS ProjectName,
            @validation_id AS ValidationId,
            CASE @status
                WHEN 1 THEN 'created'
                WHEN 2 THEN 'running'
                WHEN 3 THEN 'canceled'
                WHEN 4 THEN 'failed'
                WHEN 5 THEN 'pending'
                WHEN 6 THEN 'ended_unexpectedly'
                WHEN 7 THEN 'succeeded'
                WHEN 8 THEN 'stopping'
                WHEN 9 THEN 'completed'   -- 'completed' ist kein Standard, aber vorhanden
                ELSE 'unknown'
            END AS ValidationStatus,
            @message AS Messages;

        -- Falls die Validierung nicht innerhalb der Wartezeit abgeschlossen wurde
        IF @status NOT IN (3,4,7)
            PRINT 'WARNUNG: Validierung wurde nach ' + CAST(@maxWaitSeconds AS VARCHAR) + ' Sekunden nicht abgeschlossen.';

    END TRY
    BEGIN CATCH
        PRINT 'FEHLER bei der Validierung von ' + @projectName + ': ' + ERROR_MESSAGE();
        -- Fehlerzeile in Ergebnismenge ausgeben
        SELECT 
            @folderName AS FolderName,
            @projectName AS ProjectName,
            NULL AS ValidationId,
            'error_during_validation' AS ValidationStatus,
            ERROR_MESSAGE() AS Messages;
    END CATCH

    PRINT @CrLf;
    SET @currentId = @currentId + 1;
END