Stop SSIS Package Execution From Web Application – File to File transaction
How can I stop SSIS package execution immediately from the web application if the package contains file connection managers like flat file connection or Excel.I could do it for OLEDB connection managers by getting the particular SPID using sp_Who2 and killing it. Could anyone please suggest like that of a way for the remaining connection managers especially for file Flat File ,Excel ,File etc.
Thanks
Mohan
Thanks
Mohan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Exactly, so to make the SPID to SSIS package visible I would recommend a certain account (SQL och Windows your choice) to run your SSIS tasks. Perhaps even one account per SSIS driven application.
This way you'll be able to identify what SPID to kill!
Regards Marten
This way you'll be able to identify what SPID to kill!
Regards Marten
The information in sysprocesses might just not be sufficient unless you use a specific account to execute the SSIS package or job so that you can identify the SSIS process using that unique account otherwise it's going to be a little hard to identify.
One more thing you could do is launch SQL profiler and look up that process. I hope you know the different sub-tasks and I hope they have specific SQL which might be helpful to identify that process ID.
It's more of a gamble especially when you have nothing really distinct for that specific SSIS package otherwise identifying the process ID will be a bit difficult.
A quick way would have been assigning a unique user to run that SSIS package or job so that you simply pick up that user's processes when the package runs & terminate it.
Like I stated earlier, another option would be using profiler if you have specific T-SQL code in that SSIS package which you know and you look it up in the profiler but this will be a slower method since the package has so many other tasks so you stand a chance of missing if it has not executed that specific code for you to see it with profiler.
The choice is yours now.
Good luck.
One more thing you could do is launch SQL profiler and look up that process. I hope you know the different sub-tasks and I hope they have specific SQL which might be helpful to identify that process ID.
It's more of a gamble especially when you have nothing really distinct for that specific SSIS package otherwise identifying the process ID will be a bit difficult.
A quick way would have been assigning a unique user to run that SSIS package or job so that you simply pick up that user's processes when the package runs & terminate it.
Like I stated earlier, another option would be using profiler if you have specific T-SQL code in that SSIS package which you know and you look it up in the profiler but this will be a slower method since the package has so many other tasks so you stand a chance of missing if it has not executed that specific code for you to see it with profiler.
The choice is yours now.
Good luck.
ASKER
Didn’t get exact solution
ASKER
Thanks for your replay.
I am not able to find a particular SPID for FlatFile Connection Manager or Excel Connection Manager. If the ConnectionManagerType is ‘OLEDB’ then only I could find out a SPID associated with that.
Thanks
Mohan