#title SSISDB Query 

ssisdb에 .dtsx는 
 * ssisdb.internal.packages --> 여기가 아니라
 * ssisdb.internal.object_versions 여기에 있다. (object_data column)



{{{
with pkg
as
(
select distinct 
    a.name project_name
,   b.name package_name
from ssisdb.internal.projects a
    inner join ssisdb.internal.packages b
        on a.project_id = b.project_id
)
select b.*
from pkg aa
    cross apply (
        select 
            b.name
        ,   c.created_time
        from ssisdb.internal.projects a
            inner join ssisdb.internal.packages b
                on a.project_id = b.project_id
            inner join ssisdb.internal.object_versions c
                on b.project_version_lsn = c.object_version_lsn
        where 1=1
        and a.name = aa.project_name
        and b.name = aa.package_name
        --and c.created_time between '20190410' and '20190425'
    ) b
}}}


{{{
select 
    b.*
,   c.created_time
--,   c.object_data --이것이 .dtsx
from ssisdb.internal.projects a
    inner join ssisdb.internal.packages b
        on a.project_id = b.project_id
    inner join ssisdb.internal.object_versions c
        on b.project_version_lsn = c.object_version_lsn
where 1=1
and a.name = 'dw'
and b.name = 'dim.test.dtsx'
}}}

{{{

select 
    a.*
,   b.*
from (
    select 
        concat('\SSISDB', '\', d.name, '\', a.name, '\', b.name) full_name
    ,   d.name folder_name
    ,   a.name project_name
    ,   b.name package_name
    ,   d.folder_id
    ,   a.project_id
    ,   b.package_id
    ,   b.project_version_lsn
    ,   row_number() over(partition by concat('\SSISDB', '\', d.name, '\', a.name, '\', b.name) order by b.project_version_lsn desc) seq
    from ssisdb.internal.projects a
        inner join ssisdb.internal.packages b
            on a.project_id = b.project_id
        inner join ssisdb.internal.object_versions c
            on b.project_version_lsn = c.object_version_lsn
        inner join ssisdb.internal.folders d
            on a.folder_id = d.folder_id
    where 1=1
    and b.name = 'dim.test.dtsx'
) a
    cross apply (
        select top 1 object_data, created_time
        from ssisdb.internal.object_versions
        where object_version_lsn = a.project_version_lsn
        order by created_time desc
    ) b
where 1=1
and a.seq = 1
}}}

참고
 * https://gist.github.com/kulmam92/5939978
 * https://github.com/SparkhoundSQL/sql-server-toolbox/blob/master/ssisdb%20package%20history.sql