DTS retry with ActiveX ??
-
I've been looking up this issue for awhile now and haven't gotten to far. HEre is what I am trying to do.
I want to check for text files to see if they are there first (all of them), then if they are, run the different DTS's packages, if they aren't, retry X amount of times and if they still aren't there after X amount of times, run an activex script to send me an email.
I'm having problems with the retry part. In ActiveX, there is DTSStepScriptResult_RetryLater. But I don't know how you can set an interval delay. I don't want to create a loop because I don't want the server to be tied up, I just want it to sleep every minute (For example).
-
Is it possible to try to opposite?
Like have a job that runs every 5 minutes from let's say 9 to 10, and if at 10, the files are not there, then send a warning (if they are, the correct job would be started immediatly obviously)?
-
I'm not sure how to do that, but that would work.
9:00 -> no files
9:01 -> no files
9:02 -> files -> start dts
10:00 -> no files
10:01 -> no files
10:02 -> no files
10:03 -> no files
10:04 -> no files (no more retries) -> send error email
When I check for the files, I'm not sure how I can in activex script to retry after X amount of time. The only way I know how to do that is through the schedule job. The only issue I have with that is, Everytime it reruns the DTS package (retries), my global varibles reset back to 0, so it never reaches to 5 to send the email.
-
this could come in handy :
WAITFOR DELAY '00:00:01' --wait 1 second then resume the script
-
It didn't like WAITFOR DELAY '00:00:01' at all. Came up with an error saying waitfor and delay were not defined.
People must be doing this somewhere, check for a file, wait, check again, etc... I can't seem to find any documentation on it.
The activeX has DTSStepScriptResult_RetryLater but I can't seem to set a delay with it. RetryLater in 1 minute.
Or another approached was to set a global variable, so everytime the DTS package would run, it would count the number of tries. (Using the schedular, you can set the retries there) But everytime you run the dts, the global variable always resets back to what it was set to.
-
Did you run that as tsql??
-
Sorry I thought you meant in ActiveX. This does work in TSQL. I will play around with this and see what kind of damage the waiting does on the servers' processor, if it's too heavy or not.
-
If the wait puts to much of a demand on the server (I don't think it will) you could try something like this.
Create a table with three columns (one for the incremental retry value, one for the max retrys and one for the next date/time to perform tasks). Use an Execute SQL Task to retrieve the current values and populate your local variables. Use an ActiveX task to perform the dirty deeds
and populate another global variable indicating whether or not to retry. One last Execute SQL Task that either resets the retry value and sets the next date/time if everything is ok.Good luck,
Darrell
-
Can't answer that one... but I would suspect that they don't start an infinite loop that loads the cpu until x time has passed
. -
I do this by using the FileSystemObject to check for the existance. If the files are there, I report success for that step and then use DMO commands (oJob, oJobSchedule) to set the job start time to its original value, and continues on to the next step. If the files are not there (DTSTaskExecResult_Failure), I branch to a step that reschedules the job for 5 minutes later. It does this until 7am and if it hasn't worked by then, reschedules back to its original time and notifies me.
-
I was actually thinking of the same thing. I basically already have a table that will update it with a success or fail status. I would just have to add a little bit more to this. I have one question thought, I've tried something similar in the past.
How can a "Execute SQL Task" populate/access local vaiables and talk/share them with Active X?
-
That is exactly what I want to do. I didn't know about oJob, oJobSchedule. I will have to research that, but the way you have it, is what I am looking for to do.
The only little difference is I run the DTS many times during the day.
3:00am
6:30am
9:00am
1:00pm
6:30pm
9:00pm
But that is something small I can work out.
Could you post some clips of your code on how you reschedule the job, ?
-
Here's a trimmed down version of my step. Checks for the file and reschedules the job. On success it continues with the file load, on failure it branches to reschedule later.
Function Main()
Dim fso, oJob, oJobSchedule, oSQLServer, oSchedule, iStartTime
' -------------- Create SQLServer Object and connect ------------------
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.Connect "PRODSERVER" ,"login" ,"PW"
' -------------- Create the Job and JobSchedule Objects ---------------
Set oJob = CreateObject("SQLDMO.Job")
Set oJobSchedule = CreateObject("SQLDMO.JobSchedule")
' --------------- Load the existing job into the Job object -----------------
Set oJob = oSQLServer.JobServer.Jobs("file load (Step 1)")
' --------------- Load the existing Schedule into the JobSchedule object -----------------
Set oJobSchedule = oJob.JobSchedules("file load (Step 1)")
' --------------- Get the existing Start time -----------------
iStartTime = oJobSchedule.Schedule.ActiveStartTimeOfDay
'*********** Create the filesystem object and check for the file. ***********
Set fso = CreateObject("Scripting.FileSystemObject")
If ( fso.FileExists("F:\file1") ) Then
'Set back to 1 AM
oJobSchedule.Schedule.ActiveStartTimeOfDay = "10000"
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
End Function
-
Create your Global variables and then in the Execute SQL task key in your query, then select the Parameters option and assign them from there. Now the ActiveX script can read in the Global variables.
I have had to cheat once in a while and script out the SQL for the Execute task and then use the Disconnected Edit option to manually set the output parameters.
Good luck
-
I just came online to say I figured it out and couldn't beleive I didn't know about this before hand. (Parameters option). Would of saved many headachs.! Thanks again for all your guys help, between the two, I'm pretty sure I can nail this down.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply