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

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  May 1, 2018 at 5:09 am

  #1988743

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

  ---

• Charmer

  SSChampion

  Points: 14903

  More actions

  May 1, 2018 at 5:50 am

  #1988746

  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

  May 1, 2018 at 6:25 am

  #1988754

  Hello Phil,

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

  Thanks,
  Charmer