Evaluated variable and successfully evaluated package in VS-2017 SSIS does not work when executed in ssms

Question

In VS-2017 I have written a variable with the datatype "string". This variable uses another variable in order to determine from what moment in time data should extracted from a database.

The issue at hand is that the expression is evaluated in VS successfully, the ssis package can be executed successfully and the query itself can be executed in ssms. When the package is deployed to ssms and executed throughout the Integration Service Catalog the package doesn't work. The package fails at the first 'or' statement

the message I get from the execution reports says:

"incorrect syntax near the statement 'or'".

The evaluated value (a correct sql query) works fine when executed in ssms. I tried to place an empty string before or after the 'or' statement, didn't solve the issue. I tried to replace the 'or' statement with 'and', didn't work. Executed without the 'where' statement the packages works as expected. In another package I was unable to duplicate the error

"  select * from src where id >= "+ @[User::MaxIdStr] +" or id >= "+  @[User::MaxIdStr]

The piece of code where the bug seems to be:

where
[DateUpdated (CCUPMJ)] >= "+ @[User::Max_Upmj_Start]  +" 
or F0911.[DateUpdated (GLUPMJ)] >= "  +  @[User::Max_Upmj_Start]+"
or F560911T.[DateUpdated (GLUPMJ)] >= "+ @[User::Max_Upmj_Start]

In VS it is evaluated as:

[DateUpdated (CCUPMJ)] >= 119121 

or F0911.[DateUpdated (GLUPMJ)] >= 119121

or F560911T.[DateUpdated (GLUPMJ)] >= 119121

(119121 is the default value of the variable)

The expected result should be an successfully executed package, the actual result is an failed execution.

If someone knows how to solve this I would be very graceful!

Answer 1

Is @[User::Max_Upmj_Start] being assigned an empty string or null during package execution? Using an empty string variable, I was able to reproduce the same "incorrect syntax near the statement 'or'" error you received. Since 119121 is the default value you can use the SSIS IF statement to check the length of the variable, and substitute 119121 when there isn't a value. The LEN function checks for this below, and uses the default value when the length of @[User::Max_Upmj_Start] is less than 1. For clarification, the IF syntax is displayed right above the example expression for this variable. Since this is nested each IF statement is wrapped in parenthesis. I'm assuming you omitted the beginning of the variable with a full SELECT statement, so this example just uses a dummy SELECT * FROM ... as a placeholder. Even though it holds a number, from your expression it seems that @[User::Max_Upmj_Start] is a string variable and the example below follows this assumption. Be aware that SSIS variables do not hold null, and for a string variable an empty string will be substituted for this, so using the REPLACENULL function won't be helpful here.

IF Syntax:

Expression ? True Condition : False Condition

Variable Expression:

"SELECT * FROM ... WHERE
[DateUpdated (CCUPMJ)] >= " + 
(LEN(@[User::Max_Upmj_Start]) < 1 ? "119121" : @[User::Max_Upmj_Start]) 
+ "OR F0911.[DateUpdated (GLUPMJ)] >=  " + 
(LEN(@[User::Max_Upmj_Start]) < 1 ? "119121" : @[User::Max_Upmj_Start])+ "
OR F560911T.[DateUpdated (GLUPMJ)]  >=  " + 
(LEN(@[User::Max_Upmj_Start]) < 1 ? "119121" : @[User::Max_Upmj_Start])