_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › SsisdbQuery

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

蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2019-12-06 11:11:27

螳 谿語襷螻 企る. 覃 語企. 蠏殊蟇煙 朱 覈語 讌. 一誤 襴襦 碁襯 覿. . 覩語覃 蠍 企.