SSIS Poll Execution Status

Question

I have an SSIS package that runs another SSIS package in a Foreach container; because the contents of the container terminate as soon as it launches all the packages that it was supposed to run, I need it to wait for all of the "child" packages to complete.

So, I implemented a short wait wait loop, which basically pulls objects Executionfrom SSISDB for the identifier I'm interested in.

The problem that I am facing is that there is a calculation of the total 0 Dts.Events.FireProgress, and if I uncomment the call Dts.Events.FireInformationin a loop do, every second I get a message stating that 23 packets are still running ... unless I check the SSISDB active operations window, I see that most of them have already been completed, and 3 or 4 are actually running.

What am I doing wrong, why runningCountnot the number of actually performed performances?

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 function GetExecutionIDssimply returns the entire execution identifier for the child packages from my metadata database.

Answer 1

, . :

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);

:

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);
    }

. , , - .