Hi, I am looking for advice. Here is what I want to accomplish thru SSIS (If it is even doable) 1. Execute SP1 2. Execute SP2 Based on return value, go to step 4 3. Execute SP3 Based on return value, repeat this step 4. Execute SP4 Let me know your thoughts, share resources/guides etc. Thanks

Yes its possible. Stored procs are executed in SSIS usinng the Execute SQL Task so you will need 4 of those. You cna pass the return value into a SSIS variable and then you can then use that variable in a conditional precedence constraint to determine whether step 3 or step 4 should execute after step 2. To repeat step 3 you would need to put that Execute SQL Task inside a For Loop. That For Loop can loop until some condition evaluates to false so, for example, your loop condition might be: @[User::ValueReturnedFromSP3] == 1 Hope that helps.http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Thanks Jamie, Your response is helpful. I am trying to follow one of teh tutorial and getting error related to For Loop. Can you point me to any otehr material/sample/example? Thanks

follow Jamie's advice , and this is a sample of how to use expressions in precedence constraint to implement if condition: http://www.rad.pasfu.com/index.php?/archives/11-Implement-If-condition-in-SSIS-package.htmlhttp://www.rad.pasfu.com

Thanks Jamie, Your response is helpful. I am trying to follow one of teh tutorial and getting error related to For Loop. Can you point me to any otehr material/sample/example? Thanks What error are you getting?http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Jamie, Sorry for redirecting you to another post but here you can find more details about my issue. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2b1c3c51-28b1-4f79-80e8-e6ffda0df30f Thanks you Reza for more information and sample. SSIS is new to me , as I just strtaed so I am not familair with constructs and other details so it is definately helpful. Suppose I have bunch of SP like this, then I can execute scenario as I mentioned in my first post. I need to then run this package from SQL server agent job along with error logging and handling and mail notification. In ideal world, I should spend some time to read about SSIS but my only option right now is to implement the solution ASAP (yes, I know, I have like .000001% baked knowledge.) CREATE PROCEDURE dbo.[Return7] AS BEGIN return 7 End

Jamie, Sorry for redirecting you to another post but here you can find more details about my issue. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2b1c3c51-28b1-4f79-80e8-e6ffda0df30f Thanks you Reza for more information and sample. SSIS is new to me , as I just strtaed so I am not familair with constructs and other details so it is definately helpful. Suppose I have bunch of SP like this, then I can execute scenario as I mentioned in my first post. I need to then run this package from SQL server agent job along with error logging and handling and mail notification. In ideal world, I should spend some time to read about SSIS but my only option right now is to implement the solution ASAP (yes, I know, I have like .000001% baked knowledge.) CREATE PROCEDURE dbo.[Return7] AS BEGIN return 7 End

Suppose I have bunch of SP like this, then I can execute scenario as I mentioned in my first post. I need to then run this package from SQL server agent job along with error logging and handling and mail notification. In ideal world, I should spend some time to read about SSIS but my only option right now is to implement the solution ASAP (yes, I know, I have like .000001% baked knowledge.) So, what is your problem with doing this scenario in SSIS? you just need to know about Execute sql task, Variables, Precedence Constraint, and foreach loop. this is small part of SSIS, you can get through all these in 1 day. if you have any particular problem on implementation of this scenario let us know, we will help you on ithttp://www.rad.pasfu.com

Suppose I have bunch of SP like this, then I can execute scenario as I mentioned in my first post. I need to then run this package from SQL server agent job along with error logging and handling and mail notification. In ideal world, I should spend some time to read about SSIS but my only option right now is to implement the solution ASAP (yes, I know, I have like .000001% baked knowledge.) So, what is your problem with doing this scenario in SSIS? you just need to know about Execute sql task, Variables, Precedence Constraint, and foreach loop. this is small part of SSIS, you can get through all these in 1 day. if you have any particular problem on implementation of this scenario let us know, we will help you on ithttp://www.rad.pasfu.com

Thank you for your inputs. so far I have been able to imlpment solution locally. Ie.e achieved what I wanted to do as mentioend in original post. Now I am encountering issues liek connecting to remote DB etc. I will be posting specific question related to where I am at. Thank you again.