locked
The variable is not getting database value assigned to it don't know why? RRS feed

  • Question

  • I have a Execute SQL Task, which has a simple query
    SELECT top 1 CompRes from dbo.tmp; --This will return 1 

    Now I have created a user variable ResultTest as INT with default value as 1000

    In Properties of Exec SQL Task window I've Result Name as 0 and Variable Name as User::ResultTest 

    In the Expression I have Name and value as (DT_WSTR,250)@[User::ResultTest] -- after executing this the variable should evaluate to database value of 1, but it always evaluate to 1000 which is default value... can anyone of you help me in resolving this


    aak


    • Edited by Neilcse Tuesday, May 27, 2014 4:38 AM
    Tuesday, May 27, 2014 3:46 AM

Answers

  • Hi Surender I've added 2 images.

    Please let me know if you need more info, in the second images Expression Builder after execution when I click evaluate it is always displaying 1000 which is default value, as I am expecting it to display the database value ( 1) which I was expecting it to be captured in the variable


    aak

    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    Tuesday, May 27, 2014 4:41 AM
  • These are not variable properties but package properties.

    Please remove the expression for variable and try it. But the result set should have the Result Name as name of the column exactly fetched in the query.


    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    Tuesday, May 27, 2014 9:32 PM
  • Hi Deepak

    I am getting the result now, I dont know if I would be able to get anytime as to why I was not getting the correct result...
    (Could be the case of NullReferenceException)

    I just created new pkg level variable ResultTest2 and now this variable is displaying the correct value, Now one more thing, If at all I want to capture this variable value and send it in the email, using SendMail, how I will write this in the expression of send mail.

    I want to have this in the subject line as ResultTest2


    aak


    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    • Edited by Neilcse Wednesday, May 28, 2014 4:27 AM
    Wednesday, May 28, 2014 4:20 AM

All replies

  • It would be helpful in tracing if you upload some screen shots of the Execute SQL Task

    --------------------------------------------------------

    Surender Singh Bhadauria

    My Blog

     

    Tuesday, May 27, 2014 4:15 AM
  • Hi Surender I've added 2 images.

    Please let me know if you need more info, in the second images Expression Builder after execution when I click evaluate it is always displaying 1000 which is default value, as I am expecting it to display the database value ( 1) which I was expecting it to be captured in the variable


    aak

    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    Tuesday, May 27, 2014 4:41 AM
  • Any updates on this ? :)

    aak

    Tuesday, May 27, 2014 9:49 AM
  • Why the variable is cast again as it was already cast in the query?

    Can you remove the expression and check through a break point?


    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    Tuesday, May 27, 2014 9:53 AM
  • Hi Deepak,

    Because the User::ResultTest variable is defined as INT32, I need to convert it to String using DT_WSTR to display it in the expression.



    aak


    • Edited by Neilcse Tuesday, May 27, 2014 2:08 PM
    Tuesday, May 27, 2014 2:07 PM
  • Result Name should be the name of the column  - can you give a try?

    Please check this : https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/


    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    Tuesday, May 27, 2014 2:26 PM
  • I corrected few things, and added a script also to check what value the variable has in it... now it it not showing default value but it is showing  "-1"  which is still not correct,  the correct value should be "1" coming from database 

    with this query

    SELECT top 1 cast(CompResult as INT) as CompResult from dbo.tmp 

    After completing the execution, when I go back to the evaluate expression and press evaluate it is displaying default value as 1000 (screen2), I am worried why the variable value is not getting populated with database value ....



    • Edited by Neilcse Tuesday, May 27, 2014 3:54 PM
    Tuesday, May 27, 2014 3:33 PM
    1. uploading the variable properties screen, may be I am doing some mistake here

    aak

    Tuesday, May 27, 2014 7:59 PM
  • These are not variable properties but package properties.

    Please remove the expression for variable and try it. But the result set should have the Result Name as name of the column exactly fetched in the query.


    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    Tuesday, May 27, 2014 9:32 PM
  • Hi Deepak,

    Removed the expression completely

    I did everything but still the result is not as expected,  added a script task before and after the execute sqltask with below code 

          

    public void Main()

       {
                // TODO: Add your code here
                MessageBox.Show(Dts.Variables["User::ResultTest"].Value.ToString());
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    The before script is displaying the default value for the variable (1000) and after is again -1 




    aak


    • Edited by Neilcse Wednesday, May 28, 2014 3:45 AM
    Wednesday, May 28, 2014 3:44 AM
  • Hi Deepak

    I am getting the result now, I dont know if I would be able to get anytime as to why I was not getting the correct result...
    (Could be the case of NullReferenceException)

    I just created new pkg level variable ResultTest2 and now this variable is displaying the correct value, Now one more thing, If at all I want to capture this variable value and send it in the email, using SendMail, how I will write this in the expression of send mail.

    I want to have this in the subject line as ResultTest2


    aak


    • Marked as answer by Neilcse Wednesday, May 28, 2014 4:20 AM
    • Edited by Neilcse Wednesday, May 28, 2014 4:27 AM
    Wednesday, May 28, 2014 4:20 AM
  • I think the problem might be related to the query.

    SELECT top 1 cast(CompResult as INT) as CompResult from dbo.tmp

    How many records are there in dbo.tmp table? Unless it contains only one record...

    Without an Order By, a TOP 1 can return any record from table dbo.tmp, and it doesn't necessary return the record that you get from running it in SSMS.

    You may want to consider including a WHERE clause to filter out unwanted records too.

    Hope this helps.
    ~ J.

    Wednesday, May 28, 2014 4:20 AM
  • N

    Now when I added this, after execution the evaluate expression is giving 0 default value, is it that variable holds value only for run time and after execution it get replaced with default value??


    aak

    Wednesday, May 28, 2014 4:39 AM
  • That's by default variable characteristic. Initiation and run time values are always different. Run time values change based on the manipulations and assigns to formulae or values.

    Happy to help! Thanks. Regards and good Wishes, Deepak. http://deepaksqlmsbusinessintelligence.blogspot.com/

    Wednesday, May 28, 2014 7:06 AM
  • Thanks Deepak and All,

    Any update on how I can send the values at run time in email, the above using of expression, will this work, I am not getting the variable value printed on subject line or message body

    I've Included the screen shot above

    Regards,


    aak

    Wednesday, May 28, 2014 10:15 AM
  • Hi Any updates on this? Do I need to start a new thread?


    aak


    • Edited by Neilcse Wednesday, May 28, 2014 6:01 PM
    Wednesday, May 28, 2014 6:00 PM