Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Variable Result Display

Author  Topic 

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-26 : 23:45:14
The variable is not getting database value assigned don't know why?

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





-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 00:43:09
I have added the screen shot at the below link.. I am not sure if I can add them here, if any one want to take a look please click below link..

http://social.msdn.microsoft.com/Forums/vstudio/en-US/bd2eb431-80c3-4938-b428-09fb175c5d72/the-variable-is-not-getting-database-value-assigned-to-it-dont-know-why?forum=sqlintegrationservices



-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 13:17:45
any update or any more information needed on this?

-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 13:35:36
What kind of Connection Type are you using (Ado.Net) and what kind of ResultSet are you using (single row)?

PS: I'm not sure if I need to be registered/logged into that site, but I can't see a full size image of you screen shots.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 14:34:04
Hi Lampery,

the connection is OLEDB and it is single row, I wish I could post here the screens, there is no options

also if you can zoom in to the below link in IE or Chrome you can see the screen clearly, i checked this just now

http://social.msdn.microsoft.com/Forums/vstudio/en-US/bd2eb431-80c3-4938-b428-09fb175c5d72/the-variable-is-not-getting-database-value-assigned-to-it-dont-know-why?forum=sqlintegrationservices



-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 14:47:05
I've been using a bunch of different version of SSIS and providers lately. But, I think if you are using OLEDB and Single row, then in the Execute SQL Task Editor -> Result Set "window" set the Result Name to CompRes. That should assign the value to your variable.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 15:01:31
I am using 2008 R2

On Result set window


Result Name: CompResult and Variable Name : User::ResultTest
and SQL Query is

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

I think I am doing something wrong with the expression in the property expression editor I have

Property: Name Expression: (DT_WSTR,250)@[User::ResultTest]

there are list of option here in expression property i m not aware of .. I have tried with Name, SQLStatementSource etc.. but no use :)





-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 15:15:58
I am simply trying to get the variable populated with the query result ( one variable with one column value from the query which results in one row) and display it using script msgBox etc..

I thought it to be a simple task though.. struggle a lot.. with some basics ..

-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 15:34:59
What is the point of the expression (DT_WSTR,250)@[User::ResultTest] for setting the Name property? I'm not sure what affect that would have, but try removing that and see if the variable is populated.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 15:43:39
I removed that, and the result is -1 which is still not correct, it should be 1

Below is the code in script to display the variable content

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

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 15:51:16
I deleted all the records from the table tmp and inserted 111 for CompResult column, then executed the result is still same -1 now it should snow 111..

-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 15:51:22
One thought, verify that the Scope of the variable is correct. Other than that, I'd try moving you script task before the SQL Task and verify that you get the 1000 or what ever the default value is. That way you can verify if it is an assignment issue or something else.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 16:02:03
The variable scope is at package level, and I have uploaded the screen of variable properties at below link, may be something mistake there???
mean while I'll try your suggestion.

http://social.msdn.microsoft.com/Forums/vstudio/en-US/bd2eb431-80c3-4938-b428-09fb175c5d72/the-variable-is-not-getting-database-value-assigned-to-it-dont-know-why?forum=sqlintegrationservices

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 16:04:03
When I moved ahead the script task, it is displaying the default value 1000 in msg box... now how to capture the variable value from execute sql task and display..now to see the result..?

I added another script task with same code as previous one and now the first msg is 1000 which is default and then the second message is -1 which is disturbing :(

-Neil
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 16:21:13
If you are setting the default to 1000 and you are getting a -1, then I'd double check that your query (SELECT top 1 cast(CompResult as INT) as CompResult from dbo.tmp) is returning the correct result when you run it outside of SSIS and that SSIS is pointing at the right server and database.
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-27 : 17:31:57
out side ssis it is giving correct results, i checked that query and the connection is also good i tested that, it looks like some setting I am going wrong with..

is the variable property correct at the above posted link.. i have uploaded the screen of variable property

-Neil
Go to Top of Page

aakcse
Aged Yak Warrior

570 Posts

Posted - 2014-05-28 : 00:19:49
Thanks Lampery,

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



-Neil
Go to Top of Page
   

- Advertisement -