DTS and "Flags"
-
Hi
We have a legacy batch scheduler that runs loads of SQL jobs and a couple of backup scripts here that I want to replace with DTS.
Should all be easy except that the old system uses software flags that enable specific jobs to start.
For example, one of the jobs runs a script that backs up a database to tape. Once it has finished writing to the tape the next job in the sequence can start, but not until the tape writing has finished... the script therefore sets the "flag" and the next job wont start until this "flag" is set. There are a couple of other jobs that are external to SQL that do the same sort of thing
Does anyone have any suggestions on how I can reproduce this sort of thing using DTS?
Cheers
Sam
-
Hi, Happy New Year
There are two very basic options. The first is to generate a DTS package that performs all the tasks you need, alternatively, you could split the tasks into smaller DTS packages and schedule them as ‘Jobs’ (under management folder (if using 2000)).
In both options you have a precedence order so you can start one task when the other has finished or stop if it fails.
-
Thanks for that response Wildh and a happy new year to you.
I think you've missed my difficulty.
The flow would be that job A kicks off an external process. If (and only if) this external process completes successfully then job B automatically starts. We cannot rely on successful completion of the workflow property as the external process could be a .bat file that starts some other system processes, so the .bat file could complete OK but the system processes it initiates wouldn't.
So, the challenge is how to make the kick-off of job B happen as a response to an external-to-SQL event
Does that make sense?
Cheers
Sam
-
Ok IF the system has flags that currently (assumption here) kick off various different jobs (hopefully SQL) then create ALL the DTS packages and then schedule them (via right-click) and then modify the jobs to remove the schedules.
Why do it this way? Because you can then use your "flags" to start job A, job B, etc.. without them running themselves.
IF the current system is NOT calling SQL jobs research OSQL and command-line execution of SQL.
For both of these approaches you may want to research sp_start_job....
All the information hopefully you need to assist you can be found in BOL or here
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Sam,
I'm not clear about what you want to do in DTS. If you're doing database backups, you can use SQL Server backup commands and put them in scheduled jobs. Maybe some of the dependant 'jobs' can be put into separate steps in a single job so they don't execute until the previous step completes.
Maybe I'm missing your point, but I'm not sure that DTS is the right tool for your situation.
Greg
Greg
-
Sam,
If you have DTS packages that need to run sequentially, you can use a SQL Agent Job (Management => SQL Server Agent => Jobs in the tree on the left side of the EM console). Each DTS package can be executed as a step in the SQL Agent Job, and a job step does not begin until the previous step has completed. The SQL Agent Job can be scheduled, or can be run manually.
-
I do some flag swapping that sounds similiar to what you want. The flag swapping is between my process and another process that feeds me data. The deletion of the flag is what the other process is looking for. Once they complete their task, they create a flag which my process is looking for. Once that is detected, the process flow moves on to the next dts task.
In my dts package, I have an active-x task like so:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim fso
Dim FlagFile
Set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists ("C:\flagfile.flg") then
Set FlagFile = fso.getfile ("C:\flagfile.flg")
FlagFile.delete
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Fail
end if
set fso = nothing
End Function
Next, I have an execute process task that runs a batch file that looks for the answering flag that looks like this:
:top
C:
if exist C:\ready.flg goto endd
cscript C:\wait60.vbs 60000
goto top
:endd
(wait60.vbs is a home grown program to pause some amount of time based on the parameter. They used the wscript.sleep command. In this case wscript.sleep 60000)
Once the batch program sees ready.flg, it terminates, the execute process task completes successfully and the next dts task will execute.
Hopefully, this will give you some ideas.
Teague
-
Hi
Thanks for the replies and sorry for the delay in resonding...
Greg: I'm doing all sorts of things within the batches... database backups/executing SPs/running windows exe's. As I mentioned in my original post the idea was to replace an old legecy batch process software (Compaq Batch Scheduler) which is seriously unsupported and and not very reliable, friendly or flexible. DTS can do nearly everything that software can apart from the flag bit.
Edwin: Yep I know I can stream the steps or jobs together but what I was looking at was an external event to trigger one or more jobs....
...AJ Ahrens: Yep I will seriously look at osql or isql to kick jobs off, this is something I hadn't thought of and seems quite intersting
....Teaque: That's exactly what I was looking for. Thanks
Thanks for all your help and advice on this.
Sam
-
Dear Teaque
I am developing a multiple report generating DTS which dynamically creates spreadsheets in an Excel file.
When I run the DTS components in sequence, manually, it works. When I run it all the reports end up in the same page, and the other sheets do not get created. This does not generate an error.
My question to you is, how do I test for a spreadsheets' existence in an Excel spreadsheet for use in the manner quoted above (if exists then else wait)
Kind regards
Victor
So long, and thanks for all the fishpaste 😉
-
Hi Victor,
The short answer: I don't know.
The Long answer: If you want to check for the existence of the .xls file, you could probably use that 'if exist' syntax in my example.
If you are talking about getting INTO the spreadsheet down to the tab level, that would be beyond me. I don't know enough about Excel to know if it operates on the object model where you can actually address the smaller parts programatically.
My experience has been that DTS doesn't really play nice with trying to go from SQLServer TO something else. Usually because you end up needing to use syntax specific to that other application/language that doesn't exist in SQLServer.
Sorry, not much help here.
Teague
-
It occured to me that if you DID want to just check for the existence of the .XLS file, you wouldn't want to use that DOS mess. You would want to do it with an Active-X task in your DTS package that accesses the file system object...sort of like this:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
dim fso
dim XLSFile
set fso = CreateObject("Scripting.FileSystemObject")
if fso.fileexists (c:\BlaBlaBla.xls) then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function
=============================
In this case if the BlaBlaBla.xls exists, I finish the task successfully and move on to the next task. If it doesn't exist, I force an error and exit out the failure side. Add an 'On Failure' workflow. I really hate forcing errors like this to direct the data flow. If makes me feel cheap and dirty and just...WRONG.
I hope the new SQLServer includes a better way to do this sort of if-then-else logic.
This may have just been more noise. If so, I appologize.
Don't trust the syntax above to be perfect. Check BOL.
Good luck,
Teague
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply