I am fairly new to SSIS and just need in getting started on this project and I hope I put this under the correct forum. I need to select records from server/database/table which require several joins so I created a stored procedure. Then need to take that dataset and update different server/database/table which I need to use one parameter to select a record from first table and use that field to select the record in another table to upate. I created a second stored procedure to accept parameters, hopefully from the first stored procedure. Test both stored procedure and do work individually. Now, I am not sure which controls should use to pass the data from one stored procedure to another.

Hi Angel, 1) Execute the first stored procedure in an execute sql task and return an out parameter. 2) Execute the second stored procedure in another execute sql task with input parameter from the first execute sql task. 3) As the stored procedures have different server connections when each one is considered two execute sql tasks would do the justice. 4) But if they are in the same server and database, both of them can be executed in one execute sql . May be? Happy to help! Thanks. Regards and good Wishes, Deepak.

I'm not sure about your question yet, but if you want to run stored procedures and get results send to another procedure. you can do it in control flow and/or data flow. if you want to do it per each row of data stream you can do it inside data flow with OLEDB Command if you want to do it without consideration of data rows you can do it with Exexute sql task . But you should provide brief description of what you trying to do with more details first.http://www.rad.pasfu.com

I am getting this error when executing the 2nd stored procedure, not sure why. Try several changes. [Execute SQL Task] Error: Executing the query "EXECUTE usp_updateContact ?input, ?input, ?input" failed with the following error: "Must declare the scalar variable "@P1input".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

you should set your sql statement as below: EXECUTE usp_updateContact ?, ?, ?http://www.rad.pasfu.com

OK, I am following the directions of the above Reply from Deepak. I guess I am not understanding correctly, the first stored procedure will create a dataset of several rows of 3 fields. How is/are variable(s) to be declare to be passed to the 2nd stored procedure which needs to read the rows one at a time and process.?

you need to add variables to your package first then in execute sql task if your stored procedure select returned fields at last line, you can use ResultSet tab to map each output column to package variable. then you can use these variables in the second execute sql task's parameters tab as input parameters.http://www.rad.pasfu.com

Still confusing, going another method.