Overview: Creation of a delay, usually just a few seconds, within an SSIS package.
Need for a Time Delay
For the purpose of populating a model in Master Data Services (MDS) with members, my initial version of an SSIS package contained 4 sequential tasks:
- T-SQL Task to clear staging table (execute stored proc: mdm.udpStagingClear) (not shown below)
- Data Flow to load mdm.tblStagingMember with my member data into the model
- T-SQL Task to process (execute stored proc: mdm.udpStagingSweep)
- T-SQL Task to validate (execute stored proc: mdm.udpValidateModel)
Although the package executed without error, my members weren't always processed & validated. After some testing, I concluded SSIS was executing too quickly which didn't allow MDS to do its part. After putting two time delays in place, I no longer have any issues with this load package.
Implementing a Time Delay in SSIS
I opted to use a straightforward solution: a T-SQL task to implement the following WAITFOR statement. The DELAY argument specifies the amount of time to pass before proceeding. In my situation, I opted for 25 seconds.
WAITFOR DELAY '00:00:25'
The SSIS package looks like this:
Finding More Information
MSDN: WAITFOR (Transact-SQL)