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.
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.
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 for SSIS - this is exactly the kind of thing that SSIS is for
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
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.
