Thursday 14 April 2011

Pause in SSIS

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

Solution
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
            System.Threading.Thread.Sleep(5000);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
or in VB.net
' VB.Net code
    Public Sub Main()
        'Sleep for 5 seconds
        System.Threading.Thread.Sleep(5000)
        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

7 comments:

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

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

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

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

    ReplyDelete
  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?

    ReplyDelete
  5. Option 3 worked perfectly for me. Thanks!

    ReplyDelete

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...