| 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 querySELECT top 1 CompRes from dbo.tmp; --This will return 1 Now I have created a user variable ResultTest as INT with default value as 1000In 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 |
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-05-27 : 13:17:45
|
| any update or any more information needed on this?-Neil |
 |
|
|
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. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
|
|
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. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
Posted - 2014-05-27 : 15:01:31
|
| I am using 2008 R2On Result set windowResult Name: CompResult and Variable Name : User::ResultTestand SQL Query is SELECT top 1 cast(CompResult as INT) as CompResult from dbo.tmpI think I am doing something wrong with the expression in the property expression editor I haveProperty: 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 |
|
|
|
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 |
|
|
|
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. |
|
|
|
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 1Below 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 |
|
|
|
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 |
|
|
|
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. |
|
|
|
aakcse
Aged Yak Warrior
570 Posts |
|
|
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 |
|
|
|
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. |
|
|
|
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 |
|
|
|
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 |
|
|
|
|
Variable Result Display