SSIS passing result set from one sql task to other

i have a sql task which executes a stored procedure which has some variables in it,i want to use the result set to input to next sql tasks in ssis.how do i achieve this.
it looks like this

cretae proc sp1(@ par1 int,@par2 varchar(50),@par3 varchar(50))
as begin
declare @var1 varchar(50)
declare @var2 int
declare @var3 int

update -----

insert----

select @var1=some calculated value
select @var2=some calculated value

end

create proc sp2(@var1 varchar(50))

create proc sp3(@var2  int)

i am using a sql task to execute the sp1,i need to givs these varibles inside the sp1 as the parameters to next 2 sql tasks which executes sp2 and sp3.how can we do this.


Thanks
nani22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
add two package variable, set scope as package level , and set datatypes.let's name them var1 and var2
in first execute sql task, set result set to single row.
and in result set tab, set index for var1 as 0 and for var2 as 1
in second execute sql task , use question mark as parameter in sql command and set parameter in parameter tab with var1 .
do same thing for third execute sql task.

note that your first stored procedure should return data in this manner: select @var1,@var2 inside the stored procedure.

let me know where do you have problem on implementation
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nani22Author Commented:
but what if the sp1 returns multiple select statements besides the select variables.
0
Reza RadConsultant, TrainerCommented:
>>but what if the sp1 returns multiple select statements besides the select variables.<<
this is not good for ssis,
in fact, ssis can not analyze the result, ssis just assign first row of result to variables, index 0 will be first column , index 1 will be second column and ....
0
nani22Author Commented:
dwfnlkjdsnf
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.