I have huge SP which is executed around 6 mins in Execute SQL Task in my SSIS package. However, It's still executing from past 30 mins in SSMS query window. Any thoughts? Please

Any thoughts please ?

Hi, Is it the same environment SSMS is running on as the SSIS package? If so, please check if you have any open transactions or locks in the database table. Use DBCC OPENTRAN to check for any open transactions Use sp_lock for the lock details. Let me if this does not work. Regards, Vinaya

I have huge SP which is executed around 6 mins in Execute SQL Task in my SSIS package. However, It's still executing from past 30 mins in SSMS query window. Any thoughts? Please The likely cause is different execution plans, perhaps due to different connection SET options. See http://www.sommarskog.se/query-plan-mysteries.html. Erland's article is both long and thorough. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

I have huge SP which is executed around 6 mins in Execute SQL Task in my SSIS package. However, It's still executing from past 30 mins in SSMS query window. Any thoughts? Please The likely cause is different execution plans, perhaps due to different connection SET options. See http://www.sommarskog.se/query-plan-mysteries.html. Erland's article is both long and throrough. Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

@Vinay... Yes same environment. There are no open transactions and the database isolation level is read committed which means no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks right ? @Dan... That link is not working.

@Vinay... Yes same environment. There are no open transactions and the database isolation level is read committed which means no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks right ? @Dan... That link is not working. It looks like the period at the end was included in the url. I corrected it so the link should work now.Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Hi, Finally how much time did it take? If there is no open transaction or locks, it could be the execution plan as Dan suggested. Regards, Vinaya

@Dan...The link is working now. Thanks @Vinay... It's running forever.

Hi, Did you ever try running it again using SSIS, let me know what result you get...

Yes, I did. That SP is executed around 7 mins in SSIS.

Did you check if the parameters in both the scenarios are the same?