Listar Pacotes do Integration Services

Question

Tem algum select que lista todos os pacotes de SSIS que estão no banco?

Answer 1

TDxion,

Tente este select:

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 
INNER JOIN msdb.dbo.sysssispackagefolders AS FLD 
ON PCK.folderid = FLD.folderid 
INNER JOIN sys.syslogins AS LG 
ON PCK.ownersid = LG.sid 
ORDER BY PCK.name;

---

Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
ITIL V3 Foundation
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Twitter: @FabrizzioCaputo
Email: [e-mail]

Answer 2

TDixon123,

As pastas lógicas criadas para agrupamento de pacotes no msdb são representadas como linhas na tabela sysssispackagefolders no msdb.

Acredito que esse link possa lhe auxiliar  http://msdn.microsoft.com/pt-br/library/ms137916.aspx

---

Luciana Sampaio http://lucianasampaio.wordpress.com/

Answer 3

Perfeito Fabrizzio, era exatamente o que eu precisava. Ao executar deu erro, mas é porque eu uso SQL 2005, então tem 2 tabelas que o nome é diferente.

Ao quebrar um pouco a cabeça achei as tabelas que são para o SQL 2005:

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;

Valew...