patternsqlMinor
Time Boxing SSIS Task
Viewed 0 times
boxingssistasktime
Problem
I've an SSIS task which download a couple of files,update various Database tables and archive the files(i have it split in various packages).
The average time to completion is about 2.30 min.
However, i've recently noticed on few occassions that the SSIS Task does remain in the runnning state and unable to complete.
What i would like is to receive an email when
The average time to completion is about 2.30 min.
However, i've recently noticed on few occassions that the SSIS Task does remain in the runnning state and unable to complete.
What i would like is to receive an email when
the average time to completion is passed and after fail the package .Solution
My off the cuff thought would be that you need to turn on logging in your SSIS package and I'm assuming you have it pointed at SQL Server. I prefer to capture OnPre/PostExecute, TaskFailed and OnError events at a minimum.
Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.
Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.
Now you have a tool that when it runs detects the overlong situation and sends notification.
The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.
Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.
Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.
Now you have a tool that when it runs detects the overlong situation and sends notification.
The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.
Context
StackExchange Database Administrators Q#52119, answer score: 3
Revisions (0)
No revisions yet.