Pages

▼

Saturday, July 18, 2009

A Pause Task for SSIS

This is a relatively simple thing to accomplish in a Script, or even using an Execute SQL Task.  In fact, Jamie Thomson, with comments from Darren Gosbell, Marco Russo (I believe) and many others describes all three methods in a years old blog post - SSIS: Put a package to sleep.
Those are just fine, but they all have slight issues.  Jamie's first suggestion spikes CPU during your wait.  Using an Execute SQL Task to WAITFOR DELAY assumes you have a SQL Server available, you want to manage a connect to it, and you know they syntax of WAITFOR.  (I'm always having to look things like that up.)  Using a script assumes you know how to use .Net's Sleep and/or TimeSpan structure(one of the reasons that Jamie avoided the Script).
Another (Simpler?) Alternative
They really are minor issues, and I'm definitely not suggesting that Integration Services should add such a capability out of the box... but it does come in handy.  I typically use the WAITFOR method... no real reason, really - although I suppose I consider it a little more "transparent" than the Script method.  However, I recently had a desire not to use a SQL connection.  Note that it wasn't a "need", it was a desire - I didn't want the management overhead for a connection I wouldn't otherwise use in the package.  That, and I wanted to test the empty framework I had for converting Script Tasks to Custom Tasks.  (Yes, by doing so I did find and fix a bug in that!)

So here's a "Pause" task that allows you to specify a duration in milliseconds, or a time of day to pause until.  The binaries (2005 and 2008) are on my SkyDrive.  When I get some time, I'll post the project on CodePlex[1] so you can see how "minimal" the source really is.  Of course, all I've provided in that download is the DLL - so you'll have to install it yourself:
  • Copy it to the Program Files\Microsoft SQL Server\100\DTS\Tasks folder (or "90" for 2005)
  • Copy it (again) to the equivalent x86 Program Files folder if you're on a 64-bit system
  • Register it in the GAC with GACUtil
It has it's own simple editor to allow you to select if you want a duration to wait, or wait until a specific time of day.  Those properties are exposed in the properties window as well (as all Tasks do) - and they're usable with property expressions as well.  That way, you can provide a string value for a time of day inside an expression, perhaps retrieved into an SSIS variable - pretty much the same way you'd do it with a T-SQL WAITFOR.

For those of you who are coming to the PASS meeting this Thursday, this is one of the Tasks we could look at - just ask.  Although I'd rather convert your Scripts...

[1] UPDATE 2009-07-29: The Pause Task is now on CodePlex - installer included!
Posted at

5 comments:

  1. AnonymousMay 8, 2011 at 11:44 AM

    Hi Tod, just installed your pause tasks and works great. Fixed for me the problem for waiting on a word report from ssrs and after that continuing my ssis package. thxs Gerard

    ReplyDelete
  2. Todd McDermidMay 13, 2011 at 10:55 AM

    Randy left a comment that Blogger ate in their recent outage:
    Hi Todd, I needed a pause step and your program was just what I needed. I have been running in test mode from my system for a few weeks and didn't have any problems and it worked as advertised. We move the SSIS to production today though and this was what we received as an error messages:
    Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright Microsoft Corporation 2010. All rights reserved. Started: 1:21:56 PM Error: 2011-05-12 13:21:56.91 Code: 0xC0010018 Source: Pause Task Description: Failed to load task "Pause Task", type "ToddMcDermid.SSIS.PauseTask, PauseTask100, Version=1.0.0.0, Culture=neutral, PublicKeyToken=7415b8b05f59ca43". The contact information for this task is "Todd McDermid". End Error Error: 2011-05-12 13:21:57.32 Code: 0xC0010026 Source: Pause Task Description: The task has failed to load. The contact information for this task is "Todd McDermid". End Error Error: 2011-05-12 13:21:57.32 Code: 0xC0024107 Source: Pause Task Description: There were errors during task validation. End Error Error: 2011-05-12 13:21:57.32 Code: 0xC0010025 Source: ImportLoanCollateralData Description: The package cannot execute because it contains tasks that failed to load. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:21:56 PM Finished: 1:21:57 PM Elapsed: 0.764 seconds. The package execution failed. The step failed.

    Hi Randy - thanks for the question! My first bet is that you don't have the task installed in the 64-bit location. Check the following path for the DLL: Program Files\Microsoft SQL Server\100\Dts\Tasks.
    If that's not it - please post over on CodePlex or on the MSDN SSIS Forums.

    ReplyDelete
  3. Kevin NJuly 31, 2011 at 1:35 PM

    Todd, I love the script, but it seems to be limited to about 30 seconds maximum? Can the delay be set for longer than 30 seconds?

    ReplyDelete
  4. Todd McDermidAugust 3, 2011 at 12:02 AM

    I don't recall or currently see any reason why the limit would be 30K milliseconds. The time limit is stored in an integer variable (32 bit signed) so there should be ample room to pause for a whole day...

    ReplyDelete
  5. PedroOctober 10, 2011 at 12:22 PM

    I wish I would have seen this earlier. That error has been causing me untold issues when trying to edit a package that has your task in it. Although I had the task installed in the x86 path, I didn't have it in the usual path. The installer doesn't put it in both places, so that piece is still manual.

    ReplyDelete