Find the reference_id belonging to a package

Question

I need to find the SSISDB.catalog.environment_references.reference_id for a given package/project. I was given the query below but some for some packages it gives no result. How do I rewrite this query so it gives me the proper reference_id per package/project?

In the case of the missing results they are always omitted because of the second part of this join:

INNER JOIN SSISDB.catalog.projects AS PJ ON  PJ.folder_id = F.folder_id /* following part causes missing results but also prevents cartesian product */ AND PJ.project_id = ER.project_id

This is the query I was given and that is in use now.

SELECT  PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F2.name, PJ.name, PK.name),
        EnvironnmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F.name, E.name),
        EnvironmentReferenceID = ER.reference_id,
        ProjectFolder = F.name,
        Project = PJ.name,
        Package = PK.name,
        EnvironmentFolder = F2.name,
        Environment = E.name
        --fields below for testing
        ,er.reference_type
        ,pj.project_id as pj_project_id
        ,er.project_id as er_project_id
    FROM    SSISDB.catalog.folders AS F
        INNER JOIN SSISDB.catalog.environments AS E ON E.folder_id = F.folder_id
        INNER JOIN SSISDB.catalog.environment_references AS ER ON   (ER.reference_type = 'A' AND ER.environment_folder_name = F.name    AND ER.environment_name = E.name)
                                                                    OR
                                                                    (ER.reference_type = 'R'AND ER.environment_name = E.name)
        INNER JOIN SSISDB.catalog.projects AS PJ ON  PJ.folder_id = F.folder_id AND PJ.project_id = ER.project_id 
        INNER JOIN SSISDB.catalog.packages AS PK ON PK.project_id = PJ.project_id
        INNER JOIN SSISDB.catalog.folders AS F2 ON F2.folder_id = PJ.folder_id

Answer 1

Turns out that this query is correct. The missing project was misconfigured

SELECT  PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F2.name, PJ.name, PK.name),
        EnvironnmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F.name, E.name),
        EnvironmentReferenceID = ER.reference_id,
        ProjectFolder = F.name,
        Project = PJ.name,
        Package = PK.name,
        EnvironmentFolder = F2.name,
        Environment = E.name
        --fields below for testing
        ,er.reference_type
        ,pj.project_id as pj_project_id
        ,er.project_id as er_project_id
    FROM    SSISDB.catalog.folders AS F
        INNER JOIN SSISDB.catalog.environments AS E ON E.folder_id = F.folder_id
        INNER JOIN SSISDB.catalog.environment_references AS ER ON   (ER.reference_type = 'A' AND ER.environment_folder_name = F.name    AND ER.environment_name = E.name)
                                                                    OR
                                                                    (ER.reference_type = 'R'AND ER.environment_name = E.name)
        INNER JOIN SSISDB.catalog.projects AS PJ ON  PJ.folder_id = F.folder_id AND PJ.project_id = ER.project_id 
        INNER JOIN SSISDB.catalog.packages AS PK ON PK.project_id = PJ.project_id
        INNER JOIN SSISDB.catalog.folders AS F2 ON F2.folder_id = PJ.folder_id