narrow default width wide
colour style colour style colour style colour style

Script to find SMS Packages with bad SourcePaths

This is a simple little VBS script that will help you to find SMS Packages that are pointing to a Source Path that doesn't exist. I just threw it together but I plan to set it up as a scheduled task that runs nightly. I'll have it e-mail me whenever it finds a package without a valid Source Path so we can get it fixed right away instead of waiting for someone to call and complain.

Not a whole lot to it really. You might want to take a look at the query:

SELECT PkgSourcePath, Name FROM SMS_Package WHERE PkgSourceFlag != '1'

Only checking packages that have PkgSourceFlag something other than '1' makes sure we only check for Packages that are configured to use source files.

The script runs really fast. It checked 400+ packages in around 5 seconds. I was a little surprised that it ran that fast since it had to use the FileSystemObject to check for the existence of each one of the source paths.

Just update the server name and the Site Code for your environment....

Option Explicit
'On Error Resume Next

Dim objWMIService, SWBemlocator, colItems, objItem
Dim UserName, Password
Dim strComputer, objFSO
strComputer = "servername"
UserName = ""
Password = ""
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set SWBemlocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = SWBemlocator.ConnectServer(strComputer,"\root\sms\site_XXX",UserName,Password)
Set colItems = objWMIService.ExecQuery("Select PkgSourcePath, Name from SMS_Package where PkgSourceFlag != '1'",,48)
For Each objItem in colItems
 If objFSO.FolderExists(objItem.PkgSourcePath) Then
  'WScript.Echo "OK"
  'WScript.Echo "NO SOURCE"
  WScript.Echo objItem.Name & vbTab & objItem.PkgSourcePath
 End If