Home > SSIS Best Practices > SSIS #109 – Wait for data with For Loop Container

SSIS #109 – Wait for data with For Loop Container

Believe it or not, in the BI development world, waiting is fact of life. We often have to wait for the data is ready, or wait till it’s time to do certain things.

I had some examples in my blog showing how to use the Foreach Loop Container.

image

I often use the For Loop Container in my SSIS design to handle the wait-for scenario. One very common usage of the For Loop Container is to wait for data become ready in the source before we pull data.

In this example, I want to wait for the prior day’s data before I start to pull the data. Actually because our data source does not give us “load status”, I designed two wait-for-data steps before I start to pull the data. The first For Loop container detects the presence of the prior day’s data. The second For Loop container determines if the row counts and data counts for the past 7 days are stable or not.

image

In this blog, I’ll just show you how I configured the first For Loop Container.

Goal – IF prior day’s data is present, THEN move to next task; Otherwise, sleep for 5 minutes and check again.

Step 1 – Configure the For Loop Container

The step 0.9 is to create a variable, varDataReady_Prior_Day with an initial values of 0.

image 

There are really only two things we need to configure the For Loop Container:

  • InitExpression: @varDataReady_Prior_Day = 0; this tells SSIS that I don’t know if my data is ready or not, so go ahead start the loop.
  • EvalExpression: @varDataReady_Prior_Day == 0; this tells SSIS that if the data is not ready, please loop through again; but if the data is ready (@varDataReady_Prior_Day == 1), do not continue the loop.

image

Step 2 – Configure a task to update the variable varDataReady_Prior_Day

In my example I used an Execute SQL Task to check if the data is ready or not. If it’s ready, the task updates the variable varDataReady_Prior_Day to 1.

Again there are only two things we need to configure the Execute SQL Task to make it happen.

  • Result Set = Single row
  • Pass the SQL query result to the variable

image

image

Step 3 – Configure a Script Task to sleep for 5 minutes

image

Two is a magical number in my writing today. Again I only need to configure two things for the Script Task.

  • ReadOnlyVairables: varDelayTime; this is a variable that I set to 5.
  • Script in C#: I used C# script in SSIS 2008; SSIS 2005 only gives you VB script choice.

image

Here is the simple C# script to sleep for n (varDelayTime) minutes. The Sleep() function takes mini-seconds so there is conversion from min-sec-mini sec.

image

public void Main()
{
// sleep for 5 minutes before rechecking
Double sec = Convert.ToDouble(Dts.Variables["varDelayTime"].Value) * 60;
Int32 ms = Convert.ToInt32(sec * 1000);
System.Threading.Thread.Sleep(ms);
Dts.TaskResult = (int)ScriptResults.Success;
}

Step 4 – Configure Precedence Constraint

I want to save me 5 minutes when the data is ready on the first try. So I configured the Precedence Constraint to skip the Sleeping task if the data is ready.

 image

A side note

For less complex scenario, I also often use the SQL’s native delay function WAITFOR to achieve the wait-for goal.

This code snippet uses the TIME clause for the WAITFOR statement to wait till it’s 8:00 CST to call a SQL job on another SQL server.

image

See WAITFOR (Transact-SQL) on MSDN for more info on how to use SQL’s native WAITFOR statement.

Categories: SSIS Best Practices
  1. March 10, 2012 at 10:26 pm

    Interesting…If I were you, probably I would choose the way of “wait for” in the job(it is his work) and ETL only check whether data exist, if it goes, move on.

    Still nice to know this work around. Concern about the task “wait for”, during that time, the ETL is in running mode.Any disadvantage? If the waiting condition always can not be met, the SSIS is always in running and in memory.

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: