title: Polling SSIS execution status
body: <p>I have an SSIS package that's launching another SSIS package in a <em>Foreach container</em>; because the container reports completion as soon as it launched all the packages it had to launch, I need a way to make it wait until all "child" packages have completed.</p>
  
  <p>So I implemented a little sleep-wait loop that basically pulls the <code>Execution</code> objects off the SSISDB for the ID's I'm interested in.</p>
  
  <p>The problem I'm facing, is that a grand total of 0 <code>Dts.Events.FireProgress</code> events get fired, and if I uncomment the <code>Dts.Events.FireInformation</code> call in the <code>do</code> loop, then every second I get a message reported saying 23 packages are still running... except if I check in SSISDB's <em>Active Operations</em> window I see that most have completed already and 3 or 4 are <em>actually</em> running.</p>
  
  <p>What am I doing wrong, why wouldn't <code>runningCount</code> contain the number of <em>actually running</em> executions?</p>
  
  <pre><code>using ssis = Microsoft.SqlServer.Management.IntegrationServices;
  
  public void Main()
  {
      const string serverName = "REDACTED";
      const string catalogName = "SSISDB";
  
      var ssisConnectionString = $"Data Source={serverName};Initial Catalog=msdb;Integrated Security=SSPI;";
      var ids = GetExecutionIDs(serverName);
  
      var idCount = ids.Count();
      var previousCount = -1;
  
      var iterations = 0;
  
      try
      {
          var fireAgain = true;
  
          const int secondsToSleep = 1;
          var sleepTime = TimeSpan.FromSeconds(secondsToSleep);
          var maxIterations = TimeSpan.FromHours(1).TotalSeconds / sleepTime.TotalSeconds;
  
          IDictionary&lt;long, ssis.Operation.ServerOperationStatus&gt; catalogExecutions;
          using (var connection = new SqlConnection(ssisConnectionString))
          {
              var server = new ssis.IntegrationServices(connection);
              var catalog = server.Catalogs[catalogName];
              do
              {
                  catalogExecutions = catalog.Executions
                      .Where(execution =&gt; ids.Contains(execution.Id))
                      .ToDictionary(execution =&gt; execution.Id, execution =&gt; execution.Status);
  
                  var runningCount = catalogExecutions.Count(kvp =&gt; kvp.Value == ssis.Operation.ServerOperationStatus.Running);
                  System.Threading.Thread.Sleep(sleepTime);
  
                  //Dts.Events.FireInformation(0, "ScriptMain", $"{runningCount} packages still running.", string.Empty, 0, ref fireAgain);
  
                  if (runningCount != previousCount)
                  {
                      previousCount = runningCount;
                      decimal completed = idCount - runningCount;
                      decimal percentCompleted = completed / idCount;
                      Dts.Events.FireProgress($"Waiting... {completed}/{idCount} completed", Convert.ToInt32(100 * percentCompleted), 0, 0, "", ref fireAgain);
                  }
  
                  iterations++;
                  if (iterations &gt;= maxIterations)
                  {
                      Dts.Events.FireWarning(0, "ScriptMain", $"Timeout expired, requesting cancellation.", string.Empty, 0);
                      Dts.Events.FireQueryCancel();
                      Dts.TaskResult = (int)Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Canceled;
                      return;
                  }
              }
              while (catalogExecutions.Any(kvp =&gt; kvp.Value == ssis.Operation.ServerOperationStatus.Running));
          }
      }
      catch (Exception exception)
      {
          if (exception.InnerException != null)
          {
              Dts.Events.FireError(0, "ScriptMain", exception.InnerException.ToString(), string.Empty, 0);
          }
          Dts.Events.FireError(0, "ScriptMain", exception.ToString(), string.Empty, 0);
          Dts.Log(exception.ToString(), 0, new byte[0]);
          Dts.TaskResult = (int)ScriptResults.Failure;
          return;
      }
  
      Dts.TaskResult = (int)ScriptResults.Success;
  }
  </code></pre>
  
  <p>The <code>GetExecutionIDs</code> function simply returns all execution ID's for the child packages, from my metadata database.</p>