I have a set of about 70 stored procedures to perform data transformation. They are executed sequently through a SSIS Package. The issue I am facing is that the any one of the procedure gets stuck and I need to restart the Package again. Each time I run the Package, a different stored procedure gets stuck which has completed successfully(in few minutes) in last execution. Today I observed that one of the procedure took 32 hours to complete.
Please note that the server is being used exclusively for this process only and no other application and services are using the SQL server.
Please suggest a solution to this issue.