CODEWITHSUNDEEP
sql-servervariablesssis
Jamie Stuart Robin Parsons
Jamie Stuart Robin Parsons

Reputation: 1152

SSIS- Set Multiple variables via a single SQL task

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)

Upvotes: 9

Views: 34061

Answers (3)

Jamie Stuart Robin Parsons
Jamie Stuart Robin Parsons

Reputation: 1152

I found a solution to this by using mapping the result sets as a zero-based ordinal set. E.g. under 'Result Set' in the task properties:

result name   |   variable name
-------------------------------
     0        |       a
     1        |       b
     3        |       c
     4        |       d

This method allows me to keep my SQL statement unaltered also.

Upvotes: 1

Ritesh kumar
Ritesh kumar

Reputation: 278

Create one stored procedure with four output parameters:

CREATE SP_data(
  @x INT,
  @a int OUTPUT,
  @b int OUTPUT,
  @c int OUTPUT 
  @d int output
)
AS
  SELECT top 1 
    @a = a,
    @b= b, 
    @c  = x + y,
    @d= @d  
FROM tablename
WHERE Switch = @x

in the Execute SQL Query in the parameter mapping tab create four output parameters

the execute the proc

EXEXCUT SP_data 1,? OUTPUT,? OUTPUT,? OUTPUT, ? OUTPUT

Upvotes: -1

jazzytomato
jazzytomato

Reputation: 7214

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

enter image description here

Upvotes: 15

Related Questions