Post reply

SSISDB: finding executions from an Execute Package Task

  • September 17, 2024 at 6:01 pm

    #4459272

    I am trying to find usage and non-usage of package sin my ssisdb catalog.

    I have a decent query below, that shows me the folder/project/package, when joined to internal.executions..

    My problem is from the results, and review of some of the dtsx, there are packages with a [PackageExecution Task] inside them,

    and whatever item was inside THAT does not appear in my ssisdb's executions.

    I've tried joining the info below to other tables, but cannot seem to find those secret, behind the scenes executions.

    Has anyone tripped over this?

    If i could find a relationship for a package and the package it calls in an execution's task, i could at least infer that if the parent executed, all the children should have been as well. but i am stuck when trying to  find anything like that in the tables.

    unfortunately, the xml of the dtsx package are not directly available in SSISDB either, so i cannot shred the xml for references like that.

    I'd have to upload copies of the xml into a user table, and shred that, as an alternate, but i was hoping someone else might have seen this before.

    2024-09-17_13-55-58

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @FolderName VARCHAR(128)  = '';

    SELECT 
        fldz.name AS FolderName,
        prjz.name AS ProjectName,
        pkz.name AS PackageName,
        MAX(opers.start_time) AS LastExecutionTime
    FROM SSISDB.internal.packages pkz
        INNER JOIN SSISDB.internal.projects prjz ON pkz.project_id = prjz.project_id
        INNER JOIN SSISDB.internal.folders fldz ON fldz.folder_id = prjz.folder_id
        LEFT JOIN SSISDB.internal.executions ex ON ex.project_name = prjz.name 
            AND ex.package_name = pkz.name
        LEFT JOIN SSISDB.internal.operations opers ON ex.execution_id = opers.operation_id
    WHERE (fldz.name = @FolderName OR @FolderName = '')
    GROUP BY 
        fldz.name,
        prjz.name,
        pkz.name
    ORDER BY 
        LastExecutionTime;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 18, 2024 at 6:10 pm

    #4459659

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply