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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy