查找 SSIS 包上次修改/部署日期 - SQL Server [英] Find SSIS Package last modified/deployed date - SQL Server

问题描述

我想通过执行 SQL 查询来查找 SSIS 包的最后修改日期.例如，下面的查询按降序返回程序的最后修改日期.我期待对 SSIS 包的相同类型的查询.可以从 DB 获取这些信息吗?

I would like to find last modified date of SSIS package by executing SQL Query. For example below query returns last modified date of procedures in desc order. I am expecting same kind of query for SSIS Package. Is that possible to get those information from DB?

select 
    name,
    create_date,
    modify_date 
from sys.objects 
where type='P' 
order by modify_date desc

推荐答案

在 SSISDB 中，您可以看到在特定时间执行了哪个 VERSION_BUILD.如果您使用代码存储库(TFS?)，您可以找到具有该 version_build 的包的哪个版本，以及该版本何时存储在存储库中.那就是你想知道的修改日期.

In SSISDB you can see which VERSION_BUILD was EXECUTED at a particular time. If you use a code repository (TFS?) you can find which version of the package has that version_build, and when that version was stored in the repository. That is the modified date you want to know.

您需要的 sql 语句的 SSIS 部分可能是这样的:

The SSIS part of the sql statement you need could be thus:

use SSISDB

select  top 50 
         xs.execution_path
        ,cast(xs.start_time as datetime2(0)) as start_time
        ,x.project_version_lsn
        ,p.version_build
from    internal.executables x
join    internal.executable_statistics xs on x.executable_id = xs.executable_id
join    internal.packages p 
                on  x.project_id = p.project_id 
                and x.project_version_lsn = p.project_version_lsn
                and x.package_name = p.name

where   x.package_name = 'Package1.dtsx'
and     x.executable_name = 'Package1'

order by start_time desc

如何查询您的代码存储库是您的下一个挑战.

How to query your code repository is your next challenge.

在包的代码视图中，在前 20 行中，您会发现类似:

In the code view of the package, in the top 20 lines, you will find something like:

DTS:VersionBuild="62"

如果只需要知道最新部署的版本是什么，不管是否实际执行过，都可以查询:

If you only need to know what the latest deployed version is, regardless of whether it was actually executed, you can query:

select  max(version_build) as latest_version_build
from    internal.packages
where   name = 'Package1.dtsx'

这篇关于查找 SSIS 包上次修改/部署日期 - SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！