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

6 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

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