SSIS expression picking up variable as column then throwing error

Question

I have a list of staff members that I aggregate into a string. I want to feed the output of this string into an SSIS expression that I can then use as an sql statement.

Variable created is @User::USER

Problem is, it converts the variable to the variable name and then looks for that column.

SSIS expression is:

"SELECT
    CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) as [User]
    ,user_num
FROM [dbo].[USER_B]
where CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) in (" +  @[User::USERS] + ")"

Variable is set to evaluate as expression.

This then evaluates to:

SELECT
    CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) as [User]
    ,user_num
FROM [dbo].[USER_B]
where CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) in (USERS)

@user::USERS = 'xxx','yyy','zzz'

I'm sure it's a syntax error or settings error but am yet to resolve this.

Any help would be great.

Thanks

Get the list of users:

SELECT
    STRING_AGG(QUOTENAME(CONCAT(USER_GIVEN_NAME, ' ', USER_SURNAME),''''), ',') as USERS
FROM [Operations Stats].[dbo].[Team Members]

;

"SELECT
    CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) as [User]
    ,user_num
FROM [dbo].[USER_B]
where CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) in (" +  @[User::USERS] + ")"

I would like the @user::USER to evaluate to the 'xxx','yyy','zzz' and not think of it as USER column.

Answer 1

First of all, as @Filburt mentioned in the comment above, make sure that you didn't set Evaluate As Expression to @[User:USERS] variable.

I will suggest another method to do that, Create a variable of type string @[User::strQuery] and before the data flow task add an expression task with the following expression:

@[User:strQuery] = "SELECT
CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) as [User]
,user_num
FROM [dbo].[USER_B]
where CONCAT(RTRIM(USER_GIVEN_NAME), ' ', RTRIM(USER_SURNAME)) in (" +  @[User::USERS] + ")"

And click on the Data Flow Task and set the Delay Validation property to True.

I think that using Expression Task is more precise and you will be aware of changing variables properties.