Polling SSIS execution status

Question

I have an SSIS package that's launching another SSIS package in a Foreach container; 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.

So I implemented a little sleep-wait loop that basically pulls the Execution objects off the SSISDB for the ID's I'm interested in.

The problem I'm facing, is that a grand total of 0 Dts.Events.FireProgress events get fired, and if I uncomment the Dts.Events.FireInformation call in the do loop, then every second I get a message reported saying 23 packages are still running... except if I check in SSISDB's Active Operations window I see that most have completed already and 3 or 4 are actually running.

What am I doing wrong, why wouldn't runningCount contain the number of actually running executions?

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<long, ssis.Operation.ServerOperationStatus> catalogExecutions;
        using (var connection = new SqlConnection(ssisConnectionString))
        {
            var server = new ssis.IntegrationServices(connection);
            var catalog = server.Catalogs[catalogName];
            do
            {
                catalogExecutions = catalog.Executions
                    .Where(execution => ids.Contains(execution.Id))
                    .ToDictionary(execution => execution.Id, execution => execution.Status);

                var runningCount = catalogExecutions.Count(kvp => 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 >= 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 => 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;
}

The GetExecutionIDs function simply returns all execution ID's for the child packages, from my metadata database.

Answer 1

The problem is that you're re-using the same connection at every iteration. Turn this:

using (var connection = new SqlConnection(ssisConnectionString))
{
    var server = new ssis.IntegrationServices(connection);
    var catalog = server.Catalogs[catalogName];
    do
    {
        catalogExecutions = catalog.Executions
            .Where(execution => ids.Contains(execution.Id))
            .ToDictionary(execution => execution.Id, execution => execution.Status);

Into this:

do
{
    using (var connection = new SqlConnection(ssisConnectionString))
    {
        var server = new ssis.IntegrationServices(connection);
        var catalog = server.Catalogs[catalogName];
        catalogExecutions = catalog.Executions
            .Where(execution => ids.Contains(execution.Id))
            .ToDictionary(execution => execution.Id, execution => execution.Status);
    }

And you'll get correct execution status every time. Not sure why the connection can't be reused, but keeping connections as short-lived as possible is always a good idea - and that's another proof.