SSIS Variables not changing value?!

SSIS Variables not changing value?!

• anandss

  Any ideas guys

  This problem is driving me crazy!!!!!

  
  
  

• Manny33

  Hi Mark,

  Yes, I have it working now. Thank you very much for your assistance.

  All the best my friend.

  
  
  

• bdg

  Hi there SSIS friend.

  I am not using a loop but I expect the value of the variable to change as the single row result set (coming from my SQL Task) is going in to that variable. The SQL statement gets a maximum value from one of my database tables but the variable value has been the same since yesterday even though there is a new maximum value.

  I don't know what the problem is but my understanding is that the SQL Task would execute the my statement, i.e.

  select max(someValue) from someTable and assign the current mnax value to my variable each time the package is run.

  At the moment it's giving the same value!!! It doesn't refresh! What the hell is going on I don't get it and I am getting really annoyed now.

  
  
  

• jayneag

  Hi dreamer,

  I am going to reproduce your package and test your problem. You should expect response within 1 hour.

  Cheers, Marek

  
  

• JDev

  Hi Mark,

  Thanks for offering to help me with this problem.

  I am actually outputting the contents of the variable using a script task which basically prints the contents of the variable in a message box. I am going to investigate whether the problem is within the code of the script but I don't understand why. The code I am using to output the value is like this:

  Dim vars As Variables

  Dts.VariableDispenser.LockForRead("Res_ID")

  Dts.VariableDispenser.GetVariables(vars)

  MsgBox(vars(0).Value)

  Sounds like a straightforward enough problem to solve but this has been driving me crazy since yesterday morning!!!

  Any feedback would be much appreciated.

  
  
  

• Sarah Brian

  Hi dreameR,

  Is your SQL task located inside Loop task I have (I hope so) the similar problem with Foreach Loop task. In my case during run-time I am not able to assign different email addresses from table. I suspect bug or problem with task configuration. My problem is driving me crazy too....:-)

  Just read below....

  
  I have got the problem connected with Send Mail Task in SSIS.
  The general idea was: sending email alert to different email users. I tried to use Foreach Loop task which loops through dataset (where the emails are stored) and assign different email addresses to SSIS package variable.

  When I try to use this task inside Foreach Loop task I receive this error message: "Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "The parameter 'address' cannot be an empty string."

  The SSIS package has variable (string) which is binded with ToLine - property of Send Mail task (it was set up in Expressions tab of Send Mail task). This ToLine property should assign different emails during every loop of Foreach Loop task.

  But unfortunately is seems to be not true, because Send Mail task does not allow to assign different values to ToLine property while is in Run-Time.

  Have you discovered this quite bizzare Send Mail task behaviour before
  I appreciate every help and suggestions.

  
  
  
  

  
  

• Padmav

  Hi Dreamer,

  I tried to test your problem. I think I understood your assumptions quite good, because I managed to retrieve different values for your case.

  I created Package1 with Execute SQL & Script Task inside.

  Then I created variable "Res_ID" for Package1 Scope.

  Then I chose in SQL Task Properties Editor such options:

  Tab general:

  a) ResultSet -> Single Row,

  b) ConnectionType: OLE DB,

  c) Connection: your connection string which locates your database and SQL table,

  d) SQL statement: "select COL1=max(Res_ID) from dbo.Table_1"

  Tab Result Set:

  a) Result Name: COL1

  b) Variable Name: User::Res_ID

  That's all.

  Is Script Task I paste your source code and it started working. When I changed value from 666 to 777 in SQL Table: Table_1 where Res_ID is located - it reflected in the Message box during package execution.

  Check my settings and share your thoughts.

  Cheers, Marek

  
  
• SSIS Variables not changing value?!