dtexec.exe what are you doing? (part II)
This is the second part in a very short series covering dtexec and how to see exactly what package it is executing. Last time we covered how to see what dtexec was doing using PowerShell and WMI. This was great for checking all the different packages that might be running on a server at any given time. What we will cover this time is how to create a package that will not allow any work to be done if it is already running. If all of your jobs are called through the SQL Server Agent this is not really concern you need to have, as that job scheduler will not allow the same job to be called twice. If you have the same package imbedded in different SQL Server Agent Jobs, or you are using a third party job scheduler (that will call the same job even if it is already running), or if you are using a windows scheduler calling dtexec.exe or a mixed of all 3 this should be a concern.
Package Overview:
Alright you will notice the first object in the SSIS package is the WMI Data Reader Task. This is a underutilized task in my opinion and really opens up the possibilities of data collection for SSIS.
I use the WMI Connection Manager to connect to the WMI object I would like to query. (Details below) Its query is 'select * from WIN32_process' where it selects all of the process on the local machine to where the package is being executed. It dumps the result set into a package variable that is of type Object.
The WMI Connection Manager object is the standard connection manager with the default NameSpace (\root\cimv2). I set the Server name value to \\localhost and selected the use windows authentication check box. All pretty basic options.
The forEach loop then iterates of the record set object an runs the script task once for each row. Pulling out the Caption and CommandLine columns from the recordset and placing them into two variables WMICaption and WMICommandLine. (If you would like to see the entire record set to see what is available configure the WMI task to output its data to a text file and run it once.)
The Script task inside the forEach Loop is where all of the real work is done. Most of the work being done consists of string parsing. First I grab the processes that have a Caption of DTEXEC. Caption is the process name. Then when I have a record with a DTEXEC caption value I parse out the package name between the '/F(ile)' and the '.dtsx' values that will be there in a dtexec.exe call. I also remove any directory information that was used in the call. I output this to the screen for this example in a message box for each time there is a dtexec process. If the script detects the same package name in two different records where they match the System Package Variable 'PackageName' with the process name of DTEXEC it then sets a package level variable dtexecFlag to True. This is the indicator to run or not run the rest of the package.
Public Sub Main()
'
' Add your code here
'
'--------------------------------------------------------------
'
'SFIBICH
'9/2/2008
'This strips out the package name from a CommandLine column of a
'WMI Record set returned from Win32_process object where the
'Caption value is DTEXEC
'
'--------------------------------------------------------------
Dim WMICaption As String
Dim WMICommandLine As String
Dim WMIPackageName As String
Dim PackageName As String
Dim IndexOfSlashF As Integer
Dim IndexOfSpace As Integer
WMICaption = Dts.Variables("WMICaption").Value.ToString
WMICommandLine = Dts.Variables("WMICommandLine").Value.ToString
PackageName = Dts.Variables("PackageName").Value.ToString
If WMICaption.Trim.Length > 5 Then
If (WMICaption.Trim.ToUpper.Substring(0, 6) = "DTEXEC") Then
IndexOfslashF = WMICommandLine.ToUpper.IndexOf("/F")
IndexOfSpace = WMICommandLine.Substring(IndexOfslashF).ToUpper.IndexOf(" ") + IndexOfslashF
WMIPackageName = WMICommandLine.Substring(IndexOfSpace + 1, WMICommandLine.ToUpper.IndexOf(".DTSX") - IndexOfSpace - 1)
WMIPackageName = WMIPackageName.Substring(WMIPackageName.LastIndexOf("\") + 1)
WMIPackageName = WMIPackageName.Trim
MsgBox("WMICaption:" + WMICaption + vbCrLf + "WMICommandLine:" + WMICommandLine + vbCrLf + _
"Package Name:" + PackageName + vbCrLf + "WMIPackage Name:" + WMIPackageName + vbCrLf + _
"IndexOfslashF:" + IndexOfSlashF.ToString + vbCrLf + "IndexOfSpace:" + IndexOfSpace.ToString)
Dts.Variables("PackageCount").Value = CInt(Dts.Variables("PackageCount").Value) + 1
If (WMIPackageName = PackageName And CInt(Dts.Variables("PackageCount").Value) > 1) Then
Dts.Variables("dtexecFlag").Value = True
End If
End If
End If
Dts.TaskResult = Dts.Results.Success
End Sub
Precedence Constraints.
These are pretty straight forward one is set for Success and dtexecFlag=FALSE and the other is set to Success and dtexecFlag=TRUE.
Never Ending For Loop and Script task: This combination is to give me a way to have my example package run forever so I can test the WMI portion of this package. The loop is set to 1==1 and the script task waits 5 seconds.
There are some things to remember.
1.) DTEXEC is not executed when running a package using Visual Studio BIDS 2005. So this package will not find copies of itself running if you open two BIDS sessions and execute the package twice.
2.) Run the packages through command line to avoid the issue above and to allow the message boxes to PopUp. If you run it through SQL Agent you will get an error when it comes to the Message boxes.
3.) Read the message boxes as they pop up, you will not get the "Shut Down" message box until you run the second package, and you will have to click through two boxes prior to that.
Here are the screen shots of the package in action.
Running in BIDS with a copy of the package running in the command line in the background.
Running through the command line:
Well that's it, and you can download the package here! (I think registration is required, it redirects to a user group site sorry but I am having issues uploading to this site.)