VSTSC's Community Server

dtexec.exe what are you doing? (part II)

steve — Tue, 02 Sep 2008 16:15:00 GMT

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.)

dtexec.exe what are you doing?

steve — Mon, 01 Sep 2008 00:14:00 GMT

dtexec.exe is the program that is executed to run dtsx packages in SSIS 2005. This executable is called if you call a Integration Services package through SQL Server Agent or through the command line directly. The problem I am trying to solve today is to know what package dtexec.exe is running. When you run an SSIS package you can look in task manager and see a dtexec.exe process executing. If you only have one SSIS package executing at a time then you know which package dtexec is running. If you have an enviorment where you have two or more packages on mulitple schedules and some packages that run on demand then you are left wondering which package is a particular dtexec.exe process running? I have written a short (and do I mean short) powershell script to get this information. I hope some of you find this usefull. (This script can also be modified to handle looking into a "job number/name" if you are running your SSIS packages in some sort of parent/child framework. (more on that in a future post).

So for this example I have 1 SSIS packages, NeverEndingPackage.dtsx and another instance of NeverEndingPackage2.dtsx. (NeverEndingPackage2.dtsx is a copy of the NeverEndingPackage.dtsx) These packages are simply a for loop where 1==1 and the have a script task that waits for 5 seconds inbetween each loop.

I have called these packages through the command line, once each and you will see two dtexec.exe process in my task manager, but the question is which one is which if I needed to stop one of these processes?

Now enters PowerShell (using WMI) to solve the issue. I'll try and walk through the script below.

################################
#
# SFIBICH
# 8/21/2008
# This script gets the process of a given machine
# that are running dtexec.exe (SSIS executable)
# It then intergates the CommandLine String of the
# Win32_process object to display the SSIS package
# that is running.
#
################################
#one liner
#gwmi Win32_process |?{$_.ProcessName -eq 'dtexec.exe'} | select-object -property processId, @{Name='PackageName'; Expression={$_.CommandLine.substring($_.CommandLine.IndexOf('/F')+2,($_.CommandLine.indexOf('.dtsx')-$_.CommandLine.IndexOf('/F')-2)).trim()+'.dtsx'}}, threadCount, WS,VM | format-table

#multi liner
gwmi Win32_process |?{$_.ProcessName -eq 'dtexec.exe'} | select-object -property processId,`
@{Name='PackageName'; Expression={$_.CommandLine.substring($_.CommandLine.toUpper().IndexOf('/F')`
+2,($_.CommandLine.toUpper().indexOf('.DTSX')-$_.CommandLine.toUpper().IndexOf('/F')-2)).trim()+'.dtsx'}}, `
threadCount, WS,VM | format-table -auto

Ok, the # are just comments so nothing there.

There really is only one line of code here I just broke it into different lines using the ` at the end of each line.

Line 1 - This uses the get-wmi command (shorten to gwmi) to get the Win32_process object for this computer. (Note: you can pass -ComputerName ComputerName to get the same information on a remote machine.) IT then passes the output of this to a where-object (?) PowerShell command searching on ProcessName equal to dtexec.exe. After that it passes the filtered output to a select-object command where it selects the processID and some other things

Line2 - We create a custom property of PackageName that is a substring of the Win32_process property CommandLine where I have substringed the CommandLine to get the information between the /F and the .dtsx portion of the line. Which for a dtexec.exe process will be the name of the package. (More trimming maybe nescessary if you are runing the dtexec command from a directory that the SSIS packages are not in as it will length the file name that is passed to the command.)

Line3 - Finishing up the custom property and adding .dtsx to the name of the package since I cut it off

Line 4 - adding a few other properties like WorkingSet and Virtual Memory both in KB. Finally passing it to a format-table command to get the output in a manner that I like.

Thats it....here is the script in action:

You can see that the first package NeverEndingPackage is processID 312 and the second call to NeverEndingPackage2 is 4484. You can compare the process ID in the powershell script to that in the Task Manager. You can not match a process in task manager to a SSIS package. You can use the WMI Win32 Process object to pull more details about each SSIS package execution. Next time I'll show you how to use this type of information inside an SSIS package so that an automated job scheduler (other than SQL Server Agent) can't call your package while there is an instance of it already running.

Thats it.