Variable used as an expression and value is configured from config SQL table

Question

Variable used as an expression and value is configured from config SQL table

Charmer

SSChampion

Points: 14903
More actions
May 1, 2018 at 3:42 am

#328603

Hello friends,
I am trying to configure a value of a variable from configuration table but the value is not getting updated. I have used this variable (User::GetStoreList) as an expression in the Execute SQL statement. The output is mapped into another variable (User::StoreNo) in the result set.
What I am trying to achieve is to pass this variable as a filter into the source query which is used in the Data flow task. But somehow the variable User::GetStoreList is not overriding from configuration table instead by default it takes the package value.. The other variables are able to read the value from configuration table but they are not used as an expression. Is something to do with expression ? I mean if we use variable as an expression, won't it be override from config table?
DECLARE @StoreList VARCHAR(8000)
    SELECT
        @StoreList = COALESCE(@StoreList + ',', '') + CAST(StoreID AS VARCHAR)
    FROM [dbo].[Store_Latest]
    WHERE     AND StoreID > 0
    SELECT
        @StoreList AS StoreID

Thanks,
Charmer

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

Please add another screen shot with the Result Set node selected.
Also, are you using an OLEDB or ADO connection?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Charmer SSChampion Points: 14903 More actions · Answer 2

Hello Phil
I am using OLEDB. StoreNo variable is declared as String.

The below screenshot is from config table.

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 3

Hello Phil,

I have removed the SQL command from the expression instead used just a value. Now it works.

Thanks,
Charmer