How to map multiple row results to different variables in SSIS Execute SQL Task?

Question

I need to map 1 column frmm multiple result rows of SQL query with different variables in the same SSIS package.

For example, the output of my query is:

Category, Count  
A, 16  
B, 23  
C, 41  
D, 72  
E, 32

I want the value of Count to be assigned to 5 different variables in my package.

VariableA = 16
VariableB = 23
and so on.

I came across multiple result set to an object and then read from that using a ForEach loop container. However, that stores different results in the same variable. I want to use these variables as numbers for multiple row sampling transformations in data flow task and therefore I need them as separate variables. Is there a way to solve this and get the above outcome in SSIS?

Answer 1

Like I commented above. You can pivot your query results...

;with YourQuery as
( 
select *
from (values('A',16),('B',23),('C',41),('D',72),('E',32)) as a(Cat,Ct)
)

select A=Max(case when cat='A' then Ct else 0 end)
    ,B=Max(case when cat='B' then Ct else 0 end)
    ,C=Max(case when cat='C' then Ct else 0 end)
    ,D=Max(case when cat='D' then Ct else 0 end)
    ,E=Max(case when cat='E' then Ct else 0 end)
from YourQuery

Results:

A   B   C   D   E
16  23  41  72  32