如何使用 T-SQL 列出 Sql Server 2008 上的所有 SSIS 包 [英] How to list all SSIS packages on the Sql Server 2008 using T-SQL

问题描述

我无法通过 SQL Management Studio 连接到 SSIS 子系统，因此我正在寻找一种方法来通过 t-sql 列出所有 SSIS 包.我找到了 下面的 Sql Server 2005 查询，但是它不适用于 2008 年:

I have no access to connect to the SSIS subsystem via SQL Management Studio, so I was looking for a way to list all the SSIS packages via t-sql. I found the following query for Sql Server 2005, but it was not working for 2008:

-- List all SSIS packages stored in msdb database. 
SELECT PCK.name AS PackageName 
      ,PCK.[description] AS [Description] 
      ,FLD.foldername AS FolderName 
      ,CASE PCK.packagetype 
            WHEN 0 THEN 'Default client' 
            WHEN 1 THEN 'I/O Wizard' 
            WHEN 2 THEN 'DTS Designer' 
            WHEN 3 THEN 'Replication' 
            WHEN 5 THEN 'SSIS Designer' 
            WHEN 6 THEN 'Maintenance Plan' 
            ELSE 'Unknown' END AS PackageTye 
      ,LG.name AS OwnerName 
      ,PCK.isencrypted AS IsEncrypted 
      ,PCK.createdate AS CreateDate 
      ,CONVERT(varchar(10), vermajor) 
       + '.' + CONVERT(varchar(10), verminor) 
       + '.' + CONVERT(varchar(10), verbuild) AS Version 
      ,PCK.vercomments AS VersionComment 
      ,DATALENGTH(PCK.packagedata) AS PackageSize 
FROM msdb.dbo.sysdtspackages90 AS PCK 
     INNER JOIN msdb.dbo.sysdtspackagefolders90 AS FLD 
         ON PCK.folderid = FLD.folderid 
     INNER JOIN sys.syslogins AS LG 
         ON PCK.ownersid = LG.sid 
ORDER BY PCK.name;

经过一些研究，我找到了 2008 年的查询，所以我想分享一下.请参阅下面的答案.

After a little bit of research I figured out a query for 2008, so I'd like to share. See the answer below.

推荐答案

此查询适用于 Sql Server 2008.主要区别在于使用 msdb.dbo.sysssispackages 和使用左连接，因为一些包可能没有所有者或关联的文件夹.

This query works for Sql Server 2008. The main difference is to use msdb.dbo.sysssispackages and use left joins since some packages may have no owner or associated folder.

SELECT PCK.name AS PackageName 
      ,PCK.[description] AS [Description] 
      ,FLD.foldername AS FolderName 
      ,CASE PCK.packagetype 
            WHEN 0 THEN 'Default client' 
            WHEN 1 THEN 'I/O Wizard' 
            WHEN 2 THEN 'DTS Designer' 
            WHEN 3 THEN 'Replication' 
            WHEN 5 THEN 'SSIS Designer' 
            WHEN 6 THEN 'Maintenance Plan' 
            ELSE 'Unknown' END AS PackageTye 
      ,LG.name AS OwnerName 
      ,PCK.isencrypted AS IsEncrypted 
      ,PCK.createdate AS CreateDate 
      ,CONVERT(varchar(10), vermajor) 
       + '.' + CONVERT(varchar(10), verminor) 
       + '.' + CONVERT(varchar(10), verbuild) AS Version 
      ,PCK.vercomments AS VersionComment 
      ,DATALENGTH(PCK.packagedata) AS PackageSize 
FROM msdb.dbo.sysssispackages AS PCK 
     LEFT JOIN msdb.dbo.sysssispackagefolders AS FLD 
         ON PCK.folderid = FLD.folderid 
     LEFT JOIN sys.syslogins AS LG 
         ON PCK.ownersid = LG.sid 
ORDER BY PCK.name;

这篇关于如何使用 T-SQL 列出 Sql Server 2008 上的所有 SSIS 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！