Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

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
0
Ebcidic
Asked:
Ebcidic
  • 2
  • 2
  • 2
2 Solutions
 
Chris MConsulting - Technology ServicesCommented:
Please query the system table sysprocesses in master database or sys.processes to view the process ID based on the details retrieved assuming you have more than one SSIS package.

Once you've identified the SSIS package you want, then issue a "KILL" command and specify the process ID you want to stop immediately.

All the best.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
SSIS packages hold SPIDs like any other user/connection. If sql server agent is running it you can identify it this way, otherwise you might create a SSISUser account (Windows or SQL) and let the SSIS packages use this account when connecting. Thereby making it easy to identify the SPIDs created by SSIS connections.

Lookat: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26729632.html

A ide that might be reworked is at:
http://sqlserverpedia.com/wiki/SSIS_-_Killing_Sessions_with_SSIS

This kills long running querys, perhaps it could kill list SPIDs that belong to SSIS and kill these based on some logic. Have no time to pursuit this idea, so feel free to grab it and do something with it if you want.

//Marten
0
 
EbcidicAuthor Commented:
Hello,
      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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
0
 
Chris MConsulting - Technology ServicesCommented:
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.
0
 
EbcidicAuthor Commented:
Didn’t get exact solution
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now