Earlier on, I reported the new possibility to trace the start of a checkpoint from within profiler. I tested that new event with manual checkpoint-statements, because that's exactly what happens in the production environment of our application where we force them.
Anyway, we just discovered that with a 'normal' checkpoint, triggered by the database engine itself, the event isn't raised. You can easily simulate this behavior by comparing a profiler trace with the perform counter MSSQL:Buffer Manager-Checkpoint pages/sec on a database with some load. Compare the periodic checkpoint with one you forced yourself. Weird and disappointing.
Wednesday, June 28, 2006
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
UNION ALL
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!
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
UNION ALL
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!
Subscribe to:
Posts (Atom)