Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query to get list of SSIS packges in a Server Expand / Collapse
Author
Message
Posted Wednesday, April 6, 2011 10:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
Hi !
Is there any query to get the list of ssis Packages that reside in MSDB on a server ? Thanks in advance
Post #1089382
Posted Wednesday, April 6, 2011 10:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
Try these
/* SQL 2005 */
SELECT T1.*
FROM dbo.sysdtspackages90 AS T1
INNER JOIN (
SELECT [id], MAX([verbuild]) AS [verbuild]
FROM dbo.sysdtspackages90
GROUP BY [id]) AS T2
ON T1.[id] = T2.[id]
AND T1.[verbuild] = T2.[verbuild]


/* sql2008 */
SELECT T1.*
FROM dbo.sysssispackages AS T1
INNER JOIN (
SELECT [id], MAX([verbuild]) AS [verbuild]
FROM dbo.sysssispackages
GROUP BY [id]) AS T2
ON T1.[id] = T2.[id]
AND T1.[verbuild] = T2.[verbuild]





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1089384
Posted Wednesday, April 6, 2011 10:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
Hi! I don't have the Server engine installed on my machine so how would i extract the SSIS package list from batch server ?
Post #1089390
Posted Wednesday, April 6, 2011 10:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
RamSteve (4/6/2011)
Hi! I don't have the Server engine installed on my machine so how would i extract the SSIS package list from batch server ?


Are they all stored in msdb on this remote batch server?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1089391
Posted Wednesday, April 6, 2011 11:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 AM
Points: 163, Visits: 574
Yes they are stored on remote batch server
Post #1089455
Posted Wednesday, April 6, 2011 12:19 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
To export them to the file system from msdb, this is probably the most efficient method
http://sev17.com/2011/02/importing-and-exporting-ssis-packages-using-powershell/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1089488
Posted Monday, March 25, 2013 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
in 2012 it is:
-- 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.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;


----credit goes to Zoltan Horvath url: http://gallery.technet.microsoft.com/List-all-SSIS-packages-in-901addce/view/Discussions
Post #1435026
Posted Monday, March 25, 2013 10:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Folks, can you suggest an approach for comprehensively inventorying all SSIS packages being used (and the last time it was run) in a production environment? My understanding is that viable SSIS packages can exist outside of msdb database. . . therefore the above script is not comprehensive overview of all SSIS packages...
Post #1435036
Posted Monday, March 25, 2013 10:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 11:01 PM
Points: 17,694, Visits: 15,561
The best solution for that problem is good documentation. You need to know where the packages are being stored and what processes use said packages.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1435251
Posted Tuesday, March 26, 2013 2:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
That's good to know. These things could be anywhere, in msdb and on people's individual machines, and I need to ask....
Post #1435299
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse