May 4, 2018 at 7:17 am
Hi, I have a working package that leverages a WMI event watcher to poll a folder for a file appearing and then take actions.
The package works fine in dev and so now I want to publish it to my SQL server.
I am a little confused as to how to do this.
Do I add it as an agent job?
Does it then need "kicking off" in order for it to work?
What triggers it?
Loads of questions sorry.
Any help greatly appreciated.
Regards
Dave
May 4, 2018 at 7:24 am
david_h_edmonds - Friday, May 4, 2018 7:17 AMHi, I have a working package that leverages a WMI event watcher to poll a folder for a file appearing and then take actions.
The package works fine in dev and so now I want to publish it to my SQL server.
I am a little confused as to how to do this.
Do I add it as an agent job?
Does it then need "kicking off" in order for it to work?
What triggers it?
Loads of questions sorry.Any help greatly appreciated.
Regards
Dave
Yes, it should be an Agent job, running to a schedule. Your SQL Agent user will need permissions to the folder being monitored.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2018 at 7:28 am
Hi Phil, thanks for the reply. I presume if I schedule it for say 3 am every day, it will simply restart at that time each day?
Dave
May 4, 2018 at 7:39 am
david_h_edmonds - Friday, May 4, 2018 7:28 AMHi Phil, thanks for the reply. I presume if I schedule it for say 3 am every day, it will simply restart at that time each day?
Dave
Not necessarily. If it is still running at 3am, it will carry on running and it will not restart. The SQL Agent scheduler starts a job only if that job is not currently running.
I have implemented a similar package and, to avoid it running for days on end (which seemed to cause it to hang, for unknown reasons), I have added a timeout.
The package is scheduled to run very frequently. If no files are found, package execution times out after an hour (without causing errors or warnings) and then the job restarts, according to the schedule.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2018 at 7:44 am
I have done this before and set an alert, the alert fires the job, then you never need to schedule it, but I guess that depends on what your package is doing and whether it can be fired during business hours.
May 4, 2018 at 7:47 am
Phil Parkin - Friday, May 4, 2018 7:39 AMdavid_h_edmonds - Friday, May 4, 2018 7:28 AMHi Phil, thanks for the reply. I presume if I schedule it for say 3 am every day, it will simply restart at that time each day?
DaveNot necessarily. If it is still running at 3am, it will carry on running and it will not restart. The SQL Agent scheduler starts a job only if that job is not currently running.
I have implemented a similar package and, to avoid it running for days on end (which seemed to cause it to hang, for unknown reasons), I have added a timeout.
The package is scheduled to run very frequently. If no files are found, package execution times out after an hour (without causing errors or warnings) and then the job restarts, according to the schedule.
Hi Phil,
Did you set the time out in the agent job or in the package?
Dave
May 4, 2018 at 7:48 am
Rick-153145 - Friday, May 4, 2018 7:44 AMI have done this before and set an alert, the alert fires the job, then you never need to schedule it, but I guess that depends on what your package is doing and whether it can be fired during business hours.
Hi, the package picks up a file and then consumes it into a DB along with some validation routines.
It will only see the file once a day. so a schedule will be ok I think,
Dave
May 4, 2018 at 7:48 am
Rick-153145 - Friday, May 4, 2018 7:44 AMI have done this before and set an alert, the alert fires the job, then you never need to schedule it, but I guess that depends on what your package is doing and whether it can be fired during business hours.
Hi, the package picks up a file and then consumes it into a DB along with some validation routines.
It will only see the file once a day. so a schedule will be ok I think,
Dave
May 4, 2018 at 7:59 am
david_h_edmonds - Friday, May 4, 2018 7:47 AMPhil Parkin - Friday, May 4, 2018 7:39 AMdavid_h_edmonds - Friday, May 4, 2018 7:28 AMHi Phil, thanks for the reply. I presume if I schedule it for say 3 am every day, it will simply restart at that time each day?
DaveNot necessarily. If it is still running at 3am, it will carry on running and it will not restart. The SQL Agent scheduler starts a job only if that job is not currently running.
I have implemented a similar package and, to avoid it running for days on end (which seemed to cause it to hang, for unknown reasons), I have added a timeout.
The package is scheduled to run very frequently. If no files are found, package execution times out after an hour (without causing errors or warnings) and then the job restarts, according to the schedule.
Hi Phil,
Did you set the time out in the agent job or in the package?Dave
In the package. There is no inbuilt way of setting a timeout, as far as I know. I had to code it in a script task. Something like this
public void Main()
{
try
{
DateTime TimeoutAt = DateTime.Now.AddMinutes(timeOutMinutes);
foundFile = false;
while (DateTime.Now < TimeoutAt)
{
if (fileFound)
{
//Do stuff
foundFile = true;
break;
}
Thread.Sleep(retryDelaySeconds * 1000);
}
}
}
WriteVariable("User::FoundFile", foundFile);
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.TaskResult = (int)ScriptResults.Failure;
throw ex;
}
}
I cut this code out of a module ... so it is untested as it stands.
Handle the timeout by setting an 'Expression and Constraint' condition on the precedence constraint coming out of the script task ... @[User::FoundFile]. If the file is not found, the package simply completes without error.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2018 at 10:35 am
Many thanks, I will let you now how I get on.
Dave
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply