question

BradleySQL avatar image
BradleySQL asked

Can you make a SQL job wait for a period of time?

I have had to create a very modular SQL job where it is calling various external processes such as moving files, renaming files, deleting files, compressing a folder, and ultimately FTP'ing a zip file. What I have found is that the step to FTP the file runs before the previous step has finished creating the zip file. Is there a way to add a pause before the FTP process runs? This is on SQL 2005 SP3.
sql-server-2005sqltsql
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you all. I will use the "waitfor delay" for now and will start learning more about SSIS. We are primarily a SQL 2000 shop so I have much experience with DTS, not so much with SSIS.
0 Likes 0 ·
Tim avatar image
Tim answered
The quickest and easiest way I can think of right now is to add a wait delay as an additional step. The syntax is pretty straight forward. The time is in HOURS:MINUTES:SECONDS Say for example you want to wait 5 minutes for the previous step to complete then use the following statement. WAITFOR DELAY '00:05:00' Another thing I should mention is that you could move all this into SSIS as it gives you more control over these processes (steps).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for SSIS - this is exactly the kind of thing that SSIS is for
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered
I would recommend moving this whole thing into an SSIS package as you can control the flow from one step to another more easily and wait for results from one before progressing to another. This is what you are missing and you can only guess (and hopefully over-estimate) how long to wait with your current solution
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
Trad and Fatherjack both make good points, but have you considered powershell run from the windows scheduler? Tasks liks "moving files, renaming files, deleting files, compressing a folder, and ultimately FTP'ing a zip file" are particularly inelegant inside of sql (though I have done all of them other than compressing inside of SQL scripts myself at one time or another), but they are practiccally the type of thing powershell was built for. Powershell also interacts very well with SQL through SMO.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.