What Package is Running Under DTEXEC.exe?

2013-09-25

At one client, they have a process in which a file watcher application kicks off SSIS packages for processing.  Different packages process different files and many of them could be running concurrently.  Today, we had the need to kill one of the packages, but this turned out to be more challenging than we thought.  Which process was the right one to kill?

After a little googling, I found a good solution here.  This is using powershell to query the windows process and find out what command it is running.  i.e. to run a package from the command line for a package running in the file system: dtexec /F c:\Packagename.dtsx.

I did find all the piping and string manipulation hard to read, so I created a new version like this:

gwmi Win32_process |?{$_.ProcessName -eq 'dtexec.exe'} | select-object -Property processId, @{Name='Package'; Expression={GetPackageName $_.CommandLine}} | Format-Table -AutoSize


Function GetPackageName ($p)
{
    $start = $p.ToUpper().IndexOf('/F') + 2
    $length = $p.ToLower().IndexOf('.dtsx') - $start
    $package =  $p.Substring($start,$length).Split("\")
    Return $package.GetValue($package.GetUpperBound(0)).ToString().Trim()

The first section queries the process, filtering by process name to get only dtexec.  Then it returns the process id and the package name from the command line. The function GetPackageName parses the commandline.  Note that "/F" should be changed to "/SQ" if the package is stored in SQL server.

This returns a result that looks like this:

processId            Package                  
---------                 -------                  
    17796                Package1

     8648                  Package2

If you want to kill one of the packages, this can be done by process id:
Stop-Process 17796 

- mark

Labels: , ,