dtexec.exe what are you doing?
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.