April 6, 2011 at 10:12 am
Hi !
Is there any query to get the list of ssis Packages that reside in MSDB on a server ? Thanks in advance
April 6, 2011 at 10:15 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 10:24 am
Hi! I don't have the Server engine installed on my machine so how would i extract the SSIS package list from batch server ?
April 6, 2011 at 10:25 am
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 6, 2011 at 11:32 am
Yes they are stored on remote batch server
April 6, 2011 at 12:19 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2013 at 10:33 am
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
--Quote me
March 25, 2013 at 10:39 am
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...
--Quote me
March 25, 2013 at 10:16 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2013 at 2:01 am
That's good to know. These things could be anywhere, in msdb and on people's individual machines, and I need to ask....
--Quote me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply