I have a control flow that sets a user variable with an Execute SQL task. Everything works fine in Visual Studio but once I deploy it to the SSIS catalog the variable doesn't seem to get assigned. I'm executing it against the exact same data sources.
I'll explain exactly what is going on...
The first task in the sequence container reads the last LSN recorded for the table from a state table. If there is no entry for the table then it follows path 1 in the diagram above.
At step 2 it reads the minimum LSN for the table using the CDC functions on the source database. It then stores the LSN and the datetime for that LSN in two user variables. This is the "window start" for the data replication.
Step 3 reads the maximum change date from the destination table.
Step 4 branches if the window start is before the latest change date because this will cause duplicate data to be imported into the destination table. (Suffice to say there are reasons I can't deduplicate before loading into the destination table...)
When I run the project in Visual Studio everything works fine. It moves through steps 1-3 and then goes on to the DFT because the minimum LSN for the table is after the maximum change date in the destination table. I've used break points to ensure that the variables are actually getting set correctly.
Once I deploy the project to an SSIS catalog it follows down step 4 and records the error row. It doesn't seem to set the variable or the constraint expressions aren't using the correct value. I've tried setting the variables with the result set and as output parameters in the Execute SQL task. I can see from the log message that the maximum change date from the source table is correct.
One odd thing is that the value of the variable in the log message isn't the same as the design time value. The design time value is "1899-12-25 14:20:00' but the log message shows '1899-12-30 00:00:00'. But this may be down to me using the wrong conversion method or something... I haven't looked into it much because it should still not follow path 4.
The fact that it runs as expected in Visual Studio but has a different behaviour on he server is very confusing to me. I cannot think of what could cause that.
The packages are created using BIML but I don't think that actually has any connection. The full solution replicates over 100 tables with one package per table. The problem persists even when I try a debug deployment of only a single package.
