Foreach Loop container in SSIS suggestion

Question

I have for-each loop container in my SSIS master package There is 'Execute Package Task' under this 'Execute SQL Server Agent Job Task' is there.(It has 35 steps)

Problem: SSIS master package is starting next iteration without completion of all the steps in 'Execute SQL Server Agent Job Task'.

Please suggest an approach to start the next iteration after completion of all the steps only.

Thanks in advance :)

The Execute SQL Server Agent Job Task will simply start the job; it won't wait for it to complete. It's starting the next iteration because the task has done with you asked it to do, start the job. If you need to wait for the job to finish, you would need some kind of "pooling" process. — Larnu

KeithL KeithL · Accepted Answer · 2020-02-20T14:52:13

I know I keep throwing C# solutions at SSIS problems but this is exactly what I do to prevent a job/process to run while a job is running.

I have a function that does this check and returns a boolean true/false. Use that result to determine whether to start the job or not.

    public static bool isDatabaseUpdating()
    {
        List<string> jobs = new List<string>();

        jobs.Add("[Insert name of Job here]");
        jobs.Add("[Insert another job and so on here]");

        string sql = "exec msdb.dbo.sp_help_job @execution_status = 1";

        using (OleDbConnection conn = new OleDbConnection(cstr))
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection.Open();
                OleDbDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (jobs.Contains(rdr["name"].ToString())) return true;
                }
            }
        }

        return false;
    }

To use it set a SSIS Variable like this:

Dts.Variables["@variableName"].Value = isDatabaseUpdating();

And then in control flow set expression on path appropriately.

The real key to understanding this function is the SQL.

exec msdb.dbo.sp_help_job @execution_status = 1

That returns a dataset of jobs that are currently running.

Enhancement to your application

This is what your control flow will look like:

Foreach Loop container in SSIS suggestion

1 Answers