Create a pause in an SSIS package using T-SQL

Create a break in an SSIS package with a T-SQL query and the WAIT FOR DELAY function.

The Microsoft SSIS ETL does not offer a dedicated native component to create a pause in an SSIS package. Whether it is a few seconds or a few minutes in a control flow between 2 sequences for example. To mark a pause between the execution of several SSIS tasks, SQL queries, or containers, a good alternative is to create a loop in C# code, based on time. Similarly, with the SSIS Script Task component, it is possible to use time functions to set the pause and wait for the desired time.

However, a simpler solution is to use the Execute SQL Task and the T-SQL WAITFOR DELAY function. Or to schedule the time when the package should be finished executing. This page lists all the tutorials for learning the basics of Microsoft SSIS.

Here is an example of a Transact-SQL script to simply add a pause of a few seconds, minutes or hours before moving on to the next component. For example, it allows you to wait for a file copy or load to finish completely. To do this, set the hours, minutes and seconds directly in the SQL code or hard-coded in an SSIS variable.

1. Add and set up the SSIS components

To start, create an SSIS project with Visual Studio 2019. Then configure an OLE DB connection to a SQL Server database, in this case it is a local database so localhost is used as the server. Then add the following components:

A first data flow.
The Execute SQL Task component between the two data streams to time the execution.
The second SSIS data flow.

*Add a SQL task to create a pause in an SSIS package*

2. Set up the SSIS Execute SQL task

Configure the component with a T-SQL query to wait for the desired time, with a format of type: HH:MM:SS, in this order:

Hours
Minutes
Seconds

Use this sample SQL code to create a 5 second pause with a T-SQL script, i.e., no hours, no minutes, but a break of 5 seconds.

WAITFOR DELAY '00:00:05';

*Select the database and add the SQL code to create a 5 second pause*

3. Run the SSIS package and observe the pause time

Once the pause task is linked and configured, run the SSIS package containing the data flow and observe the 5 second pause time.

The SSIS package pauses for 5 seconds and then proceeds to the next task

Once the pause is over, the following tasks run normally, i.e., immediately. It is also possible to manage this script also with SSIS variables for more flexibility to use a dynamic timing pause, like a predefined time store in a configuration SQL Server table.

*Successful completion of the SSIS package with a pause*

Conclusion on breaks between tasks in SSIS

Using a SQL Server script is the simplest way to implement a break in an SSIS package. It does not address more complex needs such as waiting for a text file to be dropped for example. But the script can be used in a loop with precedence constraints and variables to achieve the solution.