How to programmatically kill a long running SSIS scheduled job
-
I have an SSIS package that is scheduled to run every hour to grab some data from a website and create a flat file for a vendor application to use. The problem is that occasionally it hangs (maybe the website is not available, etc) and it never completes with a success or failure therefore the next scheduled time the job can not kick off because it is "executing" still.
So I am trying to come up with a way to handle this situation. I have thought about a timer, but I have yet to find a way to make it work -- if you have any suggestions I'm all ears.
My second thought was to have another scheduled job that checks to see if this job is still running and stops the job so it can try again at the next scheduled time. However, I am not sure how to programmatically stop a SQL agent job. Can anyone tell me how to accomplish this?
I am also up for other suggestions.
Thanks
Sherri
-
Sherri Barkley (3/20/2009)
I have an SSIS package that is scheduled to run every hour to grab some data from a website and create a flat file for a vendor application to use. The problem is that occasionally it hangs (maybe the website is not available, etc) and it never completes with a success or failure therefore the next scheduled time the job can not kick off because it is "executing" still.So I am trying to come up with a way to handle this situation. I have thought about a timer, but I have yet to find a way to make it work -- if you have any suggestions I'm all ears.
My second thought was to have another scheduled job that checks to see if this job is still running and stops the job so it can try again at the next scheduled time. However, I am not sure how to programmatically stop a SQL agent job. Can anyone tell me how to accomplish this?
I am also up for other suggestions.
Thanks
Sherri
What approach do you use to connect to the website? Is it some third-party executable? Don't you have a parameter for setting timeout?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
It sort of depends on why the package is hanging. If its waiting for a file, you could use something like this (obviously alter it for your requirements).
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks
Imports Microsoft.VisualBasic.FileSystem
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic
Public Class ScriptMain
' 900 seconds is 15 mins
Public Sub Main()
Dim objFSO As FileInfo
Dim sFileName As String = CStr(Dts.Variables.Item("sFilePath").Value) & CStr(Dts.Variables.Item("WaitFileName").Value)
Dim sFilePath As String = Dts.Variables.Item("sFilePath").Value.ToString
Dim iWaitTime As Integer = CInt(Dts.Variables.Item("WaitTime").Value)
Dim iWaitIterations As Integer = CInt(Dts.Variables.Item("WaitIterations").Value)
Dim sPackageName As String
Dim sMsg As String
Dim bProduction As Boolean = CBool(Dts.Variables.Item("bProduction").Value)
Dim sOutputPath As String = Dts.Variables.Item("sOutputPath").Value.ToString
Dim sBakFile As String = sOutputPath & "scshpfdl.tab"
Dim i As Integer = 0
While Not File.Exists(sFileName) And i <= iWaitIterations
WaitABit(iWaitTime)
i = i + 1
End While
' Because we are now getting the file from the source, we do not need to kill the original file
' anymore - has to stay for other processes.
If File.Exists(sFileName) Then
File.Copy(sFileName, sBakFile, True)
Do While Not File.Exists(sBakFile)
'release processing while the file copies
Loop
' output file
Dts.TaskResult = Dts.Results.Success
Else
sPackageName = CStr(Dts.Variables.Item("System::PackageName").Value)
sMsg = sPackageName & " waited until " & Format(Now(), "Short Time") & " for the file " & Chr(34) & sFileName & Chr(34) & _
" to arrive. Please check to see if the file has arrived and when it has, rerun the package."
Dts.Variables.Item("Message").Value = sMsg
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
Public Sub WaitABit(ByVal iSeconds As Integer)
Dim Start, Finish, TotalTime As Double
Start = Microsoft.VisualBasic.DateAndTime.Timer
Finish = Start + iSeconds ' Set end time for "iSeconds" duration.
Do While Microsoft.VisualBasic.DateAndTime.Timer < Finish
' Do other processing while waiting for "iSeconds" to elapse.
Loop
TotalTime = Microsoft.VisualBasic.DateAndTime.Timer - Start
End Sub
End Class
-
Thanks, I will give that a try.
-
How did you go Sherri?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply