Hi, I am looking to execute a stored procedure with different parameters passed from a table or a execute sql task (SSIS). I want to execute this in a loop so the stored procedure executes for all the parameters. Suppose a table contains 3 columns Year Month studentid 2010 9 1234 2010 8 14 I am looking how i can execute a stored procedure like this: Exec updatestudent 2010,8,14 exec updatestudent 2010.9.1234 I am trying to add this logic as a part of a ssis package. So i want to direct the output from the stored proc to a table..Any pointers for achieving this in ssis. Thanks

You will need either a Data Flow or an Execute SQL Task that loads data from your 'control' table into a variable of type Object. Then you will need a For each loop that iterates over the values in the object (ADO recordset enumerator). And you will need to map the Results to three variables, one for each of your three parameters. Create a variable called SQL and set its EvaluateAsExpression = True, and set the Expression as "EXEC dbo.updatestudent " + (DT_STR, 4 1252)(User::Var1) + "," + ... Seed the three 'parameter' variables with values and test your Expression on the SQL variable. Now, inside the Loop, put in a Data Flow, and for the Source, set the Table Access Mode to SQL Command from variable. If the variable evaluates properly, it should work OK.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

you can do this in a data flow task too, you need a source to fetch data from source table and then connect it to an OLEDB COMMAND, in oledb command, set connection and write your execute statement there, like this: execute myStoredProcedure ?, ? ,? the question marks are parameters marker, each of them is a place holder for a parameter, now in column mappings, you can map input columns to these parameters simply.http://www.rad.pasfu.com

I concur with Reza. Simpler, less to mess up. No loops even. Go with it.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Reza has the best solution for this. Tweet me..

Thanks Reza,todd and sudeep. Reza's solution worked like a charm...I just have a last doubt, How can i redirect the output of the oledb command to a table. In the current example where i am passing 3 parameters to the stored procedure, i see only 3 output columns even though the stored procedure is returing around 10 columns. How do i redirect the result of stored procedure to a table..I apologize for being so dense

what is type of your stored procedure output? are they OUTPUT PARAMETERS ? or you just use a select at the end line of your stored procedure body and you want to fetch the result like this? in second case when you get output of stored procedure with a select statement, you can not use oledb command, so go with Todd suggestion on loop through object variables, because oledb command can not read result of stored procedure. in first case, when you have OUTPUT parameters , you can use oledb command, look at my post here on how to fetch result of output parameters in oledb command: http://www.rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.htmlhttp://www.rad.pasfu.com

My stored procedure looks something like this: Alter procedure Studentupdate (@year int,@month int,student_id int) as begin if(1=2) select convert(int,null) as year ,convert(int,null) as month, convert(int,null) as student_id,convert(varchar(10),null) as subjects,convert(datetime,null) as dob select student_id,subjects,Dob,year(join_date) as year,month(join_date) from student (nolock) where student_id=@student_id and month(join_date)=@month and year(join_date)=@year end I think this falls into your second case....So i think i should dwell more into todd's solution ?

I think this falls into your second case....So i think i should dwell more into todd's solution ? yes, go with object variable and loop scenario as todd explained. http://www.rad.pasfu.com

I will give it a try..Thank you so much for your help reza.