Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Put a package to sleep

A question was recently asked on the SSIS forum about how you can put a package "to sleep". In other words, pause execution for a defined length of time.

It was suggested to use a script task to do this. That would definately work but there is actually a much easier way - use an empty For Loop. Here's a screenshot showing how to set it up.

As you can see, all you need to do is set the EvalExpression property. It really is that easy. The expression here:

DATEADD( "ss", 10, @[System::ContainerStartTime]  ) >  GETDATE() 

will pause the package for ten seconds. It works by looping until the time that it started looping plus ten seconds is less than the current time. Very simple indeed.

 

One thing to be aware of in regards to this. In the current build of SSIS (SP1) there is a small bug that you should be aware of when using the For Loop or ForEach Loop. Each iteration of the For Loop or ForEach loop hangs onto a bit of memory - in other words there is a small memory leak. Hence if you loop enough times there is a miniscule possibility that you could get an out of memory exception. This bug has been fixed in SP2.

This bug information comes from Travis Maddox. who was running a process in an infinite loop. On each loop iteration he was processing a 100k file (approximate size). The steps outlined above for putting a package to sleep should not result in an infinite loop unless you code it up wrongly. The point being that it would take a huge processing requirement for you to encounter this bug so in all but the most extreme circumstances it really isn't anything to worry about when you are building a "sleep" For Loop that doesn't actually process any data..

My thanks go to Travis for this information.

-Jamie

 

UPDATE 2006-11-02: I neglected to check this out properly and I should make you aware that this technique may send your CPU utilization sailing a little close to the edge. Make sure you check out CPU util before you fully employ this technique.

Thanks to Darren Gosbell (and a couple of others) for pulling me up on this. And I apologise for not checking this out fully first. A lesson learnt methinks.

 

 

 

Published Monday, October 23, 2006 10:28 PM by jamie.thomson

Comments

 

DGosbell said:

What does this sort of loop do to CPU utilization? Although this is a simpler approach I would have thought a script task may have been better if you want to sleep for any significant amount of time where other processes might be running on the server.

November 2, 2006 1:14 AM
 

jamie.thomson said:

Darren,

Its a good point. I just tested it and CPU util rose to about 40%. Definately one to be aware of.

Maybe a "sleep" task is called for.

-Jamie

November 2, 2006 2:10 AM
 

sqlbi said:

I would use a script task with only one line:

System.Threading.Thread.Sleep( 10000 )

It is 10 seconds (parameter is in milliseconds), it does not use CPU and it is simpler to read (but I born as a programmer...).

Marco

November 4, 2006 6:45 PM
 

jamie.thomson said:

Marco,

In hindsight I would agree. And I'm NOT a programmer :)

-Jamie

November 4, 2006 6:51 PM
 

ronald said:

Hoi,

Probably the easiest way to do this is an 'Execute Sql Task' with a direct input query:

waitfor delay '000:01:10'

or use a  WAITFOR TIME '22:20' to start at a specific time.

Gr. Ronald

November 27, 2006 2:54 PM
 

Vijay said:

Why not a SQL Agent that invokes the SSIS package on the whole?

May 3, 2007 9:26 PM
 

Vijay said:

I mean, since the Sql agent is meant for scheduling, wont it be better suited to do this job?

May 3, 2007 9:27 PM
 

Jeff said:

If a package is dependent on an existence of a file, which is deposited on an FTP server on an 'indeterminante' time line, the above waitfor might come in handy. Can anyone think of other, more elegant way of doing what I described?

June 12, 2007 1:29 AM
 

Mick Anderson said:

Hi Guys,

DATEADD( "ss", 5, @[System::ContainerStartTime]  ) >  GETDATE()

I put the above sleep function into a for loop within another for loop.

You run this for a while and it actually causes a memorly leak.

I wanted to automate the processing of some AS cubes, basically the sleep was used within the for loop to wait until Data Warehouse had been loaded.

This seems to happen on both 32bit and 64bit SP2 versions of SSIS.  

I watched this run on 64bit server, used up to 7Gb or memory before I killed it.

Thanks

Mick

June 29, 2007 3:08 AM
 

Scott said:

I have a file watcher task.  How can I create an infinite loop around the whole package?

Basically I want to watch for a file dropped by SSRS and then send it to an FTP.  I want it ALWAYS running instead of scheduling it to run every minute in the SQL Agent.  Any ideas?

October 7, 2007 8:51 PM
 

Mike Pegurri said:

Jamie and others,

A big standing O for your post on this.  We googled " 0xc002F304 after vmware migration" and found 1 post at http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2816544&SiteID=17

which led us to your blog.

I used the waitfor delay logic in an EXEC SQL Task  and my pkg now runs fine, although I tuned it down to a 1 second wait.

For anyone else who encounters this, here's why our SSIS pkg choked. We migrated to a VM ESX  2 proc quad core machine. The SQL Server was partitioned with 30 GB , GB RAM and 2 virtual processors.  During my pkg, I fill a variable with a specific record count I'm looking for and if the count was > 0 I triggered 2 file tasks back to back to generate an alert.  I believe that the ESX and SQL Server together were too fast for the package to run these tasks side by side.  The first File Task kept failing when run in batch, but I could execute it successfully with a right click-execute.  Wierd.   After adding the delay immediately after the filling of the variable count and before the first file task, the job now works fine in batch run mode.  Thanks for the idea and this post.  I hope others see this in the future and it helps.

May 1, 2008 5:50 PM
 

jamie.thomson said:

Hey Mike. Great stuff, thanks for sharing.

May 2, 2008 9:45 AM
 

Sujata said:

Nice help...thank u

July 10, 2008 11:31 AM
 

Wayne said:

Hi all ROnalds soultion is the most elegant

July 29, 2008 1:13 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems