Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Assign a single column query value to SSIS variable Expand / Collapse
Author
Message
Posted Saturday, December 6, 2008 11:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:34 AM
Points: 21, Visits: 287
Hi

I have created the following

OLEDB Connection to the database

variable --> user::GetName

Exec SQL Task --> Select Top 1 contactname from contacts where contactname like 's%'

How do I assign this select query single row value that is Contactname to the variable User::GetName.

Thanks
Post #615235
Posted Monday, December 8, 2008 2:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:21 AM
Points: 73, Visits: 347
you need to do two things:

1) Modify your query as Select Top 1 contactname as GetName from contacts where contactname like 's%'
2)Change the 'ResultSet' property of your execute SQL task to Single row

Now go to the result set tab of your execute SQL task and map your query result to the user variable.

This should work.

Thanks
Pankaj
Post #615407
Posted Monday, December 8, 2008 4:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:34 AM
Points: 21, Visits: 287
Thanks... but doesn't work

I get the error--> The type of the value being assigned to variable "User::GetName" differs from the current variable type

I have declared user::GetName as string (under variables) and connection is OLEDB

I set Result set property to Single Row & also set the Result Name GetName to Variable Name User::GetName

Post #615834
Posted Tuesday, December 9, 2008 1:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:21 AM
Points: 73, Visits: 347
What is the datatype for the column contactname?
Check if your query is returning some data or not.
Post #615969
Posted Tuesday, December 9, 2008 3:42 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 705, Visits: 770
There is a really good tutorial page here ->

[url=http://www.sqlis.com/post/The-Execute-SQL-Task.aspx][/url]

As have been said above
1. check there is a result from the SQL
2. check the result set is set to single row.
3. on the reult set tab check the variable name is correct and that the result name is set to 0
4. Then its down to the variable type

hope this helps you.
Ells
Post #616005
Posted Tuesday, December 9, 2008 10:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:34 AM
Points: 21, Visits: 287
Thanks for the URL... good one.

My example still doesn't work.

I declared user variable as GetName String
and contactName in the table is varchar and selecting only top 1 values which is signle value.

I can see the correct values from the select statement in the watch window when I debug. But still get the Error
"The type of the value being assigned to variable "User::GetName" differs from the current variable type"
Execute SQL Task: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Post #616661
Posted Wednesday, December 10, 2008 2:12 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 705, Visits: 770
Just one question In the Execute SQL Task Editor, go to the Result Set Tab.

Result Name should be 0 and variable name User::GetName

Is this true?



( I am assuming your sql is select top1 contactname from ....)

Regards,
Mark.
Post #616775
Posted Wednesday, December 10, 2008 2:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 3:21 AM
Points: 73, Visits: 347
beats me...
if you are doing exactly what you are saying then thr is no reason it shud not work..
Post #616780
Posted Wednesday, December 10, 2008 2:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 3:34 AM
Points: 21, Visits: 287
Thanks for all your help. It works now.

I did the example at home on my home PC (didn't work at home). Tested the same copy at my office PC and didn't work either.

I created similar NEW package at office and it works.
Post #617424
Posted Thursday, June 11, 2009 3:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 8:40 AM
Points: 3, Visits: 168
YEP , it worked perfectly for me..i was selecting the RESULTSET to None instead of SINGLE ROW...........thanks a ton PANKAJ...........

---PRIYANKO
Post #733387
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse