SSIS – Working with 3 Constraints Where 2 Should Be OR and One Should Be AND

ssis-2016

So I have an SSIS flow in which I am working with three constraint within a task, but 2 of them must be an OR and one must be an AND. In pseudo-code would be something like:

if ( (constraint-a OR constraint-b) AND constraint-c)

Problem is, we can only set OR/AND to all precedents:

Are there any solutions in which we could group some of these constraints?

Best Answer

Here is my original solution for this problem, which apparently is not as bad as I thought. The only problem with it is that the designer canvas can get quite crowded.

I managed to achieve the desired solution with a little kludge, which gives me exactly what I want. However I can't help but feel a little dirty doing it, so I wonder if there is a better way. Here is a diagram of what I have done:

NOTE: It is important to emphasise that I need Main Script B to start as soon as Main Script A completes.

Script Task B1 is the one requiring the complex constraint. As you can see in the centre of the diagram, I grouped failure of Main Script A and completion of Script Task A2 in an empty container.

This allows for script tasks A1 and A2 to be bypassed if Main Script A fails, and Script Task B1 to be executed after previous script tasks have been completed. But Script Task B1 must wait for Main Script B to finish as well.

Here are the results using this cheap kludge:

When Main Script A succeeds:

When Main Script A fails:

I wonder if there are alternatives to this solution, though.

Related Solutions

SQL Server 2016 – SSISDB AlwaysOn Support and Failover Monitor Job Issues

Consider wrapping the statement in the BEGIN END block inside of sp_executeSQL so that it bypasses the syntax checking until runtime.

DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
IF @role = 1
BEGIN
    exec sp_executeSQL N'DECLARE @status tinyint;
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N''YourServerName'', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]'
END

Note: edited the script

SSIS – Stored Procedure Logs Counters in Debug Mode but Not from Agent Job

What this appeared to boil down to was the TargetServerVersion property for the project. What I hadn't realised was, where SSDT had been upgraded, the TargetServerVersion defaults to 'SQL Server 2017', whereas my target instance is 2016.

I originally tried to "downgrade" the target server version in the original project, but was still getting the same problem. I was also getting some informational messages about the script task in the child package, saying that something hadn't gone as planned when "downgrading". I eventually recreated the entire project with the TargetServerVersion set to 2016 from the beginning. This has done the trick, as all is working as it should.

The only other thing I changed was that I deployed the project using SQL Server 2016 Deployment Wizard, instead of 2017 as before. However, I'm not too sure this would have been the problem.

Moral of the story, always check your TargetServerVersion.

I have provided this as the answer, in case other people experience such an issue. If, in future, any evidence presents itself to indicate that the above was not the cause of the issue, I will gladly change the answer.

Best Answer

Related Solutions

SQL Server 2016 – SSISDB AlwaysOn Support and Failover Monitor Job Issues

SSIS – Stored Procedure Logs Counters in Debug Mode but Not from Agent Job

Related Question