SSIS Parallel execute not running sequence container

0
votes

I am writing a SSIS package to allow us to execute our ssis tasks in parallel. I have a control system that manages which packages to execute. The packages are grouped into packages that can be executed at the same time (ie not dependent on any other package in the group), and ordered by these groupings.

I am getting all the packages to execute along with their group into a table which I am using as a queue table. I then getting all the groups into an object and looping though the groups in a ForEach loop. Within this FEL, I have got 2 sequence containers. These containers have variables that are scoped to them. I get the next package from the queue table based on the group required. Parallel Execute package

The usual behavior is that the first group that executes in the loop works well, with packages running on SEQ0 and SEQ1.
The issue is coming during the execute of the next group, where only one sequence container executes, so there is no parallel execute. It can alternate with either 0 or 1 executing, but the other one does not start. I added some logging to the "Get Execution Attributes" stored proc as I was wondering if this was returning no rows on one side stopping the execute, but there was no log, so it is not executing at all on that side.

Does anyone have any idea why this would be happening that only one sequence container would execute?

2
ssis
I've made a change that looks like it works, at least in my testing. I set the "DelayValidation" property to True on both of the sequence containers. I'll give an update once I am sure this solves the issue. - ManxShred
Just to encourage you - I did something very similar in SSIS 2008 and it worked really well. - Nick.McDermaid
Well, the delay validation seems to work well in VS, but did not make any difference when it was running automated via the SSIS catalog. I have also changed the first "SQL - Get execution attributes" to delay validation, which has helped, but I have also seen some groupings that have not parallelized. - ManxShred

2 Answers

0
votes

Put SEQ-0 and SEQ-1 inside another sequence container.

0
votes

Okay, so I found the issue, and it was not a SSIS issue, despite my earlier logging showing that this was the issue.

Basically the stored proc that retrieves the next package to execute was causing an issue. I was doing a Update of a CTE to get the next row like this:

with CTE AS (
SELECT 
    TOP(1) 
    SSISPackageKey
    , IsProcessed
FROM 
    QueueTable WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE
    IsProcessed = 0
ORDER BY
    ExecuteOrder ASC)
UPDATE 
    CTE
SET
    IsProcessed = 1
OUTPUT
    inserted.SSISPackageKey INTO @SSISPackage;

There seems to be a problem in here when the 2 parallel streams execute at the exact same time. Most of the time the one process will get a row and return the package to execute, and the other one will skip all of the rows and return nothing. I think the lock is being escalated to a page lock, which then is read past, which means there is no work for the thread to do.

My simple solution for now is to add a "WAITFOR DELAY "00:00:01" before the start of the SQL - Parallel 1, which means at the start of the parallel execute there is no contention on the table, resulting in good parallel executes. Very frustrating!