Creating a Time Delay in SSIS when Loading Master Data Services

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:

  1. T-SQL Task to clear staging table (execute stored proc:  mdm.udpStagingClear)  (not shown below)
  2. Data Flow to load mdm.tblStagingMember with my member data into the model
  3. T-SQL Task to process (execute stored proc:  mdm.udpStagingSweep)
  4. 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:  Importing Data (Master Data Services)