Thursday, 14 April 2011

Pause in SSIS

How can I pause or delay the Control Flow of my package?

There is an open source pause task at codeplex, but there are three other way's to create a delay in your package.

1) For Loop Container
Add an empty For Loop Container to your control flow and one of the following expressions.
Use this expression to wait 5 seconds:
DATEADD("ss", 5, @[System::ContainerStartTime]) > GETDATE()
or 2 minutes:
DATEADD("mi", 2, @[System::ContainerStartTime]) > GETDATE()
Add the next task behind the container and it get's a 5 second delay.

2) Script Task
Add a Script Task to your control flow and add the following C# code:
// C# Code
        public void Main()
            // Sleep for 5 seconds
            Dts.TaskResult = (int)ScriptResults.Success;
or in
' VB.Net code
    Public Sub Main()
        'Sleep for 5 seconds
        Dts.TaskResult = ScriptResults.Success
    End Sub

3) Execute SQL Task
Add an Execute SQL Task to your Control Flow. Add a connection to a random SQL Server and add the following Transact-SQL statement:
-- T-SQL
WAITFOR DELAY '00:00:05'
Waiting in SSIS
Let me know if you thought of a different way to wait for a few seconds.

Note: the For Loop pause is the most processor intensive method. OK for a few seconds, but not for long periods


  1. Thank you. I want to pause it until the operator click a yes/no window. is that possible?

    1. Not very common for SSIS, but possible with a messagebox in a Script Task. Search for: " c# messagebox yes no"

  2. the for loop container works perfectly. thank you.

  3. Nice - worked for me, and kept me from having to use a third-party add-in. Thank you for sharing.

  4. HI- I'm not very familiar with SSIS. I understand the for loop container expression. I'm not sure which expression field to put it in. Can you share that information?

  5. Option 3 worked perfectly for me. Thanks!


Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...