SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assign a single column query value to SSIS variable


Assign a single column query value to SSIS variable

Author
Message
JK-211484
JK-211484
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
Panks-913490
Panks-913490
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 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
JK-211484
JK-211484
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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
Panks-913490
Panks-913490
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 347
What is the datatype for the column contactname?
Check if your query is returning some data or not.
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1202 Visits: 931
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
JK-211484
JK-211484
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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.
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1202 Visits: 931
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.
Panks-913490
Panks-913490
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 347
beats me...
if you are doing exactly what you are saying then thr is no reason it shud not work..
JK-211484
JK-211484
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 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.
M J-477094
M J-477094
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 182
YEP , it worked perfectly for me..i was selecting the RESULTSET to None instead of SINGLE ROW...........thanks a ton PANKAJ...........

---PRIYANKO
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search