I have an SSIS package which just triggers an Stored Proc. This SSIS package is runnign for more 2 Hours. If you run just a Stoer Proc in management studio its completing in an 20 min. let me know if anyone faced the similar type of issue. Store Pro has some Inset into statement. Thanks in advance. Praveen

Is your stored procedure wrapped around the Execute SQL Task? Is the sp on a SQL Server instance?Arthur My Blog

As Author is eluding to, we need more details about the procedure and the package. Is the procedure bringing in a lot of data first in order to perform the insert. Is the comparison of running SSMS times vs. the package have the same network path? Is the task in a data flow and the buffer modified to handle a large amount of data possibly? Is the procedure itself tuned already? Is there maybe an event handler on the stored procedure (execute sql task) that is firing too much? Also check waits on the SQL Server. It may be a network_io wait which may indicate it simply is an ssis configuration change needed.Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!

it is wrapped in a ExecuteSQl task. and it involves many join joins and subqueries in the sp before we do an insert statement. it is on sql server 2008 instance.

yes, you are correct !! Its brigging a lot of data ussing joins and subqueries, Store proc is wrapped in an Execute SQl Task. Both the package and proc are executed in the sme network path. Package has no event handlers enabled on any of the tasks. Procedure is tuned well, there is absolutely no problem with the SP. its just executing in 15- 20 while i execute it in the SSMS. where can i set this configuration ' It may be a network_io wait which may indicate it simply is an ssis configuration change needed.' Thanks in advance.

Basically, if you see a difference between the execution times in SSMS vs. SSIS package you are doing your test involving different conditions. Under the conditions I mean either you are running your package remotely whereas you use the SSMS right on the SQL Server instance itself or the volume of data is different between runs. Please eliminate those differences and make sure SQL Server is not overloaded at the time you are running the package. Re-do the test and come back with your findings. Also, I wanted to tell that applying the latest SPs and KBs often helps mitigate some performance problems. Arthur My Blog