Assign a single column query value to SSIS variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Assign a single column query value to SSIS...

11 posts, Page 1 of 2

1 2 »»

Assign a single column query value to SSIS variable

Rate Topic

Display Mode

Topic Options

Author

Message

JK-211484

Posted Saturday, December 06, 2008 11:49 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 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

Panks-913490

Posted Monday, December 08, 2008 2:36 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, June 09, 2013 10:36 PM
Points: 70, Visits: 333

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

JK-211484

Posted Monday, December 08, 2008 4:01 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 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

Panks-913490

Posted Tuesday, December 09, 2008 1:59 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, June 09, 2013 10:36 PM
Points: 70, Visits: 333

What is the datatype for the column contactname?
Check if your query is returning some data or not.

Post #615969

Ells

Posted Tuesday, December 09, 2008 3:42 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:29 AM
Points: 386, Visits: 686

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

JK-211484

Posted Tuesday, December 09, 2008 10:05 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 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

Ells

Posted Wednesday, December 10, 2008 2:12 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:29 AM
Points: 386, Visits: 686

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

Panks-913490

Posted Wednesday, December 10, 2008 2:23 AM

Valued Member

Group: General Forum Members
Last Login: Sunday, June 09, 2013 10:36 PM
Points: 70, Visits: 333

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

Post #616780

JK-211484

Posted Wednesday, December 10, 2008 2:57 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 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

M J-477094

Posted Thursday, June 11, 2009 3:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:50 PM
Points: 3, Visits: 165

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions