{"id":25593,"date":"2023-05-08T06:25:00","date_gmt":"2023-05-08T04:25:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=25593"},"modified":"2023-05-16T13:47:28","modified_gmt":"2023-05-16T11:47:28","slug":"create-pause-in-ssis-using-t-sql","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssis\/create-pause-in-ssis-using-t-sql\/","title":{"rendered":"Create a pause in an SSIS package using T-SQL"},"content":{"rendered":"\n

Create a break in an SSIS package with a T-SQL query and the WAIT FOR DELAY function.<\/em><\/strong><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

However, a simpler solution is to use the Execute SQL Task<\/em><\/strong> and the T-SQL WAITFOR DELAY<\/em><\/strong> 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<\/a>.<\/p>\n\n\n\n

Here is an example of a Transact-SQL script<\/a> 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.<\/p>\n\n\n\n

1. Add and set up the SSIS components<\/h2>\n\n\n\n

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

    \n
  1. A first data flow.<\/li>\n\n\n\n
  2. The Execute SQL Task<\/strong><\/em> component between the two data streams to time the execution.<\/li>\n\n\n\n
  3. The second SSIS data flow.<\/li>\n<\/ol>\n\n\n
    \n
    \"Add
    Add a SQL task to create a pause in an SSIS package<\/em><\/figcaption><\/figure><\/div>\n\n\n

    2. Set up the SSIS Execute SQL task<\/h2>\n\n\n\n

    Configure the component with a T-SQL query to wait for the desired time, with a format of type: HH:MM:SS<\/strong>, in this order:<\/p>\n\n\n\n