我无法通过SQL Management Studio访问连接到SSIS子系统,因此我正在寻找一种通过t-sql列出所有SSIS包的方法。我发现了适用于Sql Server 2005的以下查询,但它无法在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的查询语句,现在分享如下答案。