Assign a single column query value to SSIS variable

Question

Post reply

Assign a single column query value to SSIS variable

JK-211484

Right there with Babe

Points: 745
More actions
December 6, 2008 at 11:49 pm

#124927

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
August 23, 2017 at 2:01 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Panks-913490 SSCrazy Points: 2131 More actions · Answer 1

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 Right there with Babe Points: 745 More actions · Answer 2

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 SSCrazy Points: 2131 More actions · Answer 3

What is the datatype for the column contactname?

Check if your query is returning some data or not.

Ells SSCrazy Eights Points: 9901 More actions · Answer 4

There is a really good tutorial page here ->

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 Right there with Babe Points: 745 More actions · Answer 5

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 SSCrazy Eights Points: 9901 More actions · Answer 6

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 SSCrazy Points: 2131 More actions · Answer 7

beats me...

if you are doing exactly what you are saying then thr is no reason it shud not work..

JK-211484 Right there with Babe Points: 745 More actions · Answer 8

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 SSC Enthusiast Points: 165 More actions · Answer 9

YEP , it worked perfectly for me..i was selecting the RESULTSET to None instead of SINGLE ROW...........thanks a ton PANKAJ...........

---PRIYANKO

Henk Standhardt Valued Member Points: 53 More actions · Answer 10

When the SSIS Variable type is String, and you use OLEDB connection for the Execute SQL Task, the datatype of the result of the query should be Nvarchar or Nchar.

The Convert to NVarChar did the job for me...

SELECT TOP 1

CASE WHEN [StatusVarchar] IS NULL THEN 'Geen' ELSE CONVERT(NVARCHAR, StatusVarchar) End AS Result

FROM [dbo].[SSIS_Status]

WHERE StatusProjectVariant = 'mah_v2'

AND StatusSleutel = 'Exception'

Jordan R Valued Member Points: 73 More actions · Answer 11

For me, I was getting this same error because my query was unintentionally returning a varchar(max) data type. Apparently that will cause SSIS to return this error as well. I fixed it by casting the value in the query.

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 12

Is the original data column a varchar(max)? If so, this shows some of the potential hazard with using that data type instead of restricting it to some appropriate amount.