There are many reasons for terminating a long running SSIS Package. Picture a scenario whereby an inexperienced DBA/developer accidentally kicks-off a monthly job instead of a daily job, inadvertently impacting SQL Server performance. Whatever the reasons, you are more than likely to encounter a situation in which you have to temporary stop a long running process.
Although SQL Server makes it easy to terminate undesired processes, you are likely to receive ambiguous error log messages for your actions depending on the mechanism used to terminate that process. In this article we take a look at how stopping a long running Integrations Services Catalog package can be logged differently when doing it from SQL Server Management Studio (SSMS) versus Integration Services Server (ISS).
A case in point
To simulate a long running package, I setup an SSIS demo project that contains Package Sleep.dtsx which uses a Script Task. The Script Task uses a Thread.Sleep method to add a 10-minute time delay for every package execution. The main method of the Package Sleep.dtsx is shown in Script 1.
|
1 2 3 4 5 6 7 |
public void Main() { Thread.Sleep(600000); Dts.TaskResult = (int)ScriptResults.Success; } |
Script 1
My SSIS demo project was then deployed into a local instance of SQL Server 2016 and a SQL Agent job – SSISDemoJob – Sleep – was created with a single step whose sole purpose is to execute the Package Sleep.dtsx. Figure 1 shows the location of the newly deployed SSISDemo project and newly created SSISDemoJob – Sleep.
Integrations Services Catalog has a built-in All Executions report that basically list recent executions. We will be using this report to retrieve details about the package execution. The report can be accessed as shown in Figure 2.
Cancel package execution from SSMS
Let’s start off by running the job SSISDemoJob – Sleep. Say we are later informed that we shouldn’t have run this job and as a result we have to stop it. All that is required to stop it, is by simply clicking the Stop Job option as shown in Figure 3.
Figure 3 Now, if we go through All Executions report, we can notice that stopping the SQL Agent job generated an Unexpected Termination status as shown in Figure 4.
Figure 4 The error message logged in the Catalog is in contrast to the message logged in the SQL Agent job as it can be seen in Figure 5 – the agent log is more accurate as we did stop the job.
Figure 5 Stopping package execution by clicking the Stop operation in SQL Agent is equivalent to running a Kill T-SQL command. To demonstrate this, I reran the SSISDemoJob – Sleep again. Instead of clicking the Stop Job option in the SQL Agent, I retrieved the process id (58) for this operation from Activity Monitor as shown in Figure 6.
Figure 6 I then ran a T-SQL Kill command as shown in Script 2.
Script 2 As it can be seen in Figure 7, the killing of process id 58 led to the same message being logged as when we had stopped the execution using the SQL Agent – Unexpected Termination.
Figure 7 What is more impressive however, is that the SQL Agent is again correctly logging what happened to the execution of this job. As it can be seen in Figure 8 – the reason the job (and subsequently the package) was terminated was because of a kill state that was invoked.
Figure 8 Cancel package execution from SSISDB
The previous section demonstrated that when package execution is terminated within SSMS (i.e. SQL Agent Job or T-SQL KILL command) then you should use the information logged in SQL Agent job history to learn more on the causes of the termination. In this section we take a look at the various options to terminate a package within Integration Services Server (ISS). Again, we go back to SQL Agent job and rerun SSISDemoJob – Sleep.
ISS’s SSISDB keeps track of all operations that are currently active/executing. In order to retrieve a list of all active operations, you need to right click SSISDB and choose Active Operations as shown in Figure 9.
Figure 9 The Active Operations window comes up as shown in Figure 10. You can then click the Stop button located at the bottom right of the window.
Figure 10 Now let’s take a look at the status of the message that is logged following what we did. As it can be seen in Figure 11, a Canceled status was generated for stopping the package execution via the Active Operations window.
Figure 11 When we go back to the SQL Agent, we can see in Figure 12 that the job was stopped due to failure but the error message of the job goes a step further and advises that a source of the error came from the IS Server and further suggests that you go through the All Executions report for more information.
Figure 12 Similarly to using a Kill command, you can stop an operation in the ISS by using the T-SQL command. SSISDB has a built-in stored procedure called [catalog].[stop_operation] in which as the name suggests – stops an active operation. Thus, all that happened when you click the Stop button in Active Operations dialog box was essentially execute the stop operation stored procedure.
To demonstrate this, I reran the SSISDemoJob – Sleep job again. I also got a list of active operations in SSISDB but instead of clicking the Stop button I took note of the operations ID as shown in Figure 13 (in my case, the operation id is 20038).
Figure 13 I then used this operation ID as an input parameter to the [catalog].[stop_operation] stored procedure as shown in Script 3.
123456USE SSISDBGOEXEC [catalog].[stop_operation] 20038Script 3
After successfully executing Script 3, Figure 14 shows that another row has been added with a Canceled status. This confirms that stopping package execution using the Active Operations window is equivalent to executing the [catalog].[stop_operation] stored procedure.
Figure 14 Conclusion
In this article we have demonstrated different ways to stop a runaway SSIS Package. It was demonstrated that the SQL Agent job history is a reliable source of information when the runaway package is terminated using SQL Agent and a Kill T-SQL command. Alternatively, Integrations Services Server becomes a reliable source for details relating to a package execution that is stopped using Active Operations window or the stop operation stored procedure.
Downloads
Reference
- Thread.Sleep Method
- catalog.stop_operation (SSISDB Database)
- Integration Services (SSIS) Server and Catalog
Sifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.
Sifiso's LinkedIn profile
View all posts by Sifiso W. NdlovuLatest posts by Sifiso Ndlovu (see all)
