Which SSIS Package is DTEXEC running right now?

Have you ever noticed a rogue DTEXEC process, but didn’t know which of many SSIS packages was actually running, or what values had been passed to it? I was in that position once, and one of the few references that I could find was dtexec.exe what are you doing? It got me started, but that was just the tip of the iceberg; there is much more information available. Let’s use a little Powershell and WMI to get with the full command that started DTEXEC:

$cSharp = @"
using System.Management;
using System;
public class LocalDTEXEC {
  public void ListAll() {
    WqlObjectQuery wqlQuery = new WqlObjectQuery("SELECT * FROM Win32_Process WHERE Name = 'dtexec.exe'");
    ManagementObjectSearcher searcher = new ManagementObjectSearcher(wqlQuery);

    foreach (ManagementObject process in searcher.Get()) {
      Console.WriteLine("DTEXEC ProcessID: {0}", process["ProcessId"]);
      Console.WriteLine(new String('-', 80));
      string[] args = process["CommandLine"].ToString().Split('/');
      foreach(string arg in args) {
        if (arg.Trim().Length > 0)
          Console.WriteLine(@"\" + arg);
      }
    }
    Console.WriteLine(new String('-', 80));
    Console.WriteLine("Inspection Complete");
  }

  public void ListProperties() {
    // Get the WMI class
    ManagementClass processClass = new ManagementClass("Win32_Process");
    processClass.Options.UseAmendedQualifiers = true;

    // Get the properties in the class
    PropertyDataCollection properties = processClass.Properties;

    // display the properties
    Console.WriteLine("Win32_Process Property Names: ");
    foreach (PropertyData property in properties) {
      foreach (QualifierData q in property.Qualifiers) {
        if(q.Name.Equals("Description"))
          Console.WriteLine(processClass.GetPropertyQualifierValue(property.Name, q.Name));
      }
      Console.WriteLine();
    }
  }
}
"@

Add-Type -TypeDefinition $cSharp -reference System.Management
$LocalDTEXECObject = New-Object LocalDTEXEC
#$LocalDTEXECObject.ListProperties();
$LocalDTEXECObject.ListAll();

OK, so I cheated a little and pulled C-Sharp in to do the heavy lifting for me, but I think it’s a lot more readable that way. The script is only dumping out the CommandLine. If you want to see some of the other information available, just uncomment the .ListProperties call. When you are done experimenting, you can completely remove ListProperties because it isn’t needed for ListAll to work.

Here is some sample output of catching DTEXEC in action, showing exactly how it was launched:

DTEXEC ProcessID: 12776
--------------------------------------------------------------------------------
\FILE "C:\temp\DataOgreEncrypted.dtsx"
\DECRYPT mysecretpassword
\CHECKPOINTING OFF
\SET "\Package.Variables[User::RootFolder].Properties[Value]";"\\localhost\Packages"
\REPORTING E
--------------------------------------------------------------------------------
Inspection Complete