Tuesday, June 20, 2006

Deployed package version

You develop SSIS-packages from within the Business Intelligence Development Studio (or better, just call it Visual Studio) and you use Management Studio for deploying and managing packages on a certain environment. Simple question I got today from a colleague: how do you determine the version that is deployed on a server?

In the old days with DTS-packages that was straightforward, but now with SSIS, I couldn't find the elegant answer straightaway. Yes, you can export the package to the file system and open it in VS, but that's no decent solution. Querying msdb like the solution below - it's an option:

WITH Folder (FolderID, ParentFolderID, FolderName) AS
SELECT folderid, parentfolderid, cast(foldername as varchar(max))
FROM msdb.dbo.sysdtspackagefolders90
WHERE parentfolderid IS NULL


SELECT f.folderid, f.parentfolderid, cast(pf.FolderName + '\' + f.foldername as varchar(max))
FROM msdb.dbo.sysdtspackagefolders90 f
INNER JOIN Folder pf on pf.FolderID = f.parentfolderid
SELECT f.FolderName, p.name as PackageName, p.vermajor, p.verminor, p.verbuild
FROM msdb.dbo.sysdtspackages90 p
INNER JOIN Folder f on f.FolderID = p.folderid

That was the best I could come up with at that time, but at home I have more time to look into it. And actually it's quite simple, because of the great Summary-window (F7) in SSMS. It contains one report (General) when you connect to SSIS, click on a package and there is all the info you can consult in the system tables. Powerful stuff!

No comments: