SSIS- Set Multiple variables via a single SQL task

Question

I'm trying to set multiple variables to the result of an SQL Query that returns a single row with multiple columns. The SQL statement is in the format of:

SELECT top 1 
    a,
    b, 
    c = x + y,
    d  
FROM tablename
WHERE aSwitch = 1

So I wish to use an 'Execute SQL Query' task which will set 4 package variables with the results of the query.

For example, if the results of the query were:

|    a    |     b   |    c    |  d  |
-------------------------------------
|   duck  |   cow   | rabbit  |  42 |

Then the state of the variables after execution would be:

var1 = duck
var2 = cow
var3 = rabbit
var4 = 42

Any ideas?

(using VS/SQL 2005)

Answer 1

In the SQL task, under General menu, set the ResultSet property to SingleRow.

Then, in the ResultSet menu, add the variables in the order of your select clause and map the aliases with the variables. For exemple :

SELECT 1 AS One, 2 AS Two