April 2, 2012 at 2:10 am
Hello,
I need a way to keep my ssis package running once it has completed, my ssis package consists of script tasks several steps that are triggered every hour or every 30 mins, However I need a method to keep the ssis package running so it is constantly checking the time. In essence I need it to reset itself once it has completed. At the moment my package does monitor the time and executes the scripts but once it has completed it just stops and I have start it again manually or set a reoccuring schedule to sql agent, however I'd like it to be constantly running, like a windows service
I was thinking of using a timer event, but I cant see a timer task in the tool box (Im using sql 2005). Can i script a timer event or is there an alternative method I could use?
Thanks
April 2, 2012 at 2:30 am
Hello, after doing some research I think a 'For Loop' container would suit my requirement. For example if I set the InitExpression to be GetDate + 10 seconds and then set the EvalExpression to be GetDate < InitExpression, this should keep the tasking inside the For Loop container running while my expression is more than the current date.
April 2, 2012 at 12:27 pm
You may not need to rig something like that up. What event are you waiting to see happen before your Script Tasks do work? Are you by chance polling for a file every 10 seconds, and if there is a file process it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 2:05 am
opc.three (4/2/2012)
You may not need to rig something like that up. What event are you waiting to see happen before your Script Tasks do work? Are you by chance polling for a file every 10 seconds, and if there is a file process it?
Yes, pretty much so,I need to poll for several files at different intervals for example. poll for file A every hour, file b every 30 mins, file C every 2 hours.
April 3, 2012 at 2:51 am
AIRWALKER-375999 (4/3/2012)
opc.three (4/2/2012)
You may not need to rig something like that up. What event are you waiting to see happen before your Script Tasks do work? Are you by chance polling for a file every 10 seconds, and if there is a file process it?Yes, pretty much so,I need to poll for several files at different intervals for example. poll for file A every hour, file b every 30 mins, file C every 2 hours.
In cases like this, instead of 'waiting for a file to run once', I personally prefer to setup a different job for each filetype and schedule it that way. The only time I build file waits is when it's a 'once a day' kind of thing, usually, or a 'load immediately on receipt' which does 5 second polls. The reason is job logging is usually much kinder on that kind of scenario.
Most of the time when something like this comes down the requirements list, I'll ask the local programmers to build a simple waiting task that can be run at the OS level and can call the DTS package, instead of having a never-ending job in SQL Agent. The reason being we use jobs to detect 'overlong' jobs that are running and might be stalled out on something odd. For a job like this, that literally will never end except on failure, it's impossible to tell if that's happened. It's very easy to have a task like that automatically startup on reboot/restart, as well, with the startup folder on the OS.
There are also SP_ procs you can use to have it start a SQL Agent job so it can't step on itself accidentally, depends on how tight your security is and what is allowed by the DBAs.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 3, 2012 at 8:49 am
Evil Kraig F (4/3/2012)
AIRWALKER-375999 (4/3/2012)
opc.three (4/2/2012)
You may not need to rig something like that up. What event are you waiting to see happen before your Script Tasks do work? Are you by chance polling for a file every 10 seconds, and if there is a file process it?Yes, pretty much so,I need to poll for several files at different intervals for example. poll for file A every hour, file b every 30 mins, file C every 2 hours.
In cases like this, instead of 'waiting for a file to run once', I personally prefer to setup a different job for each filetype and schedule it that way. The only time I build file waits is when it's a 'once a day' kind of thing, usually, or a 'load immediately on receipt' which does 5 second polls. The reason is job logging is usually much kinder on that kind of scenario.
Most of the time when something like this comes down the requirements list, I'll ask the local programmers to build a simple waiting task that can be run at the OS level and can call the DTS package, instead of having a never-ending job in SQL Agent. The reason being we use jobs to detect 'overlong' jobs that are running and might be stalled out on something odd. For a job like this, that literally will never end except on failure, it's impossible to tell if that's happened. It's very easy to have a task like that automatically startup on reboot/restart, as well, with the startup folder on the OS.
There are also SP_ procs you can use to have it start a SQL Agent job so it can't step on itself accidentally, depends on how tight your security is and what is allowed by the DBAs.
Thanks for the advice, the requirement have come from managers who dont have a IT back ground and dont really understand how ssis is supposed to work. I have created a ssis package that meets the requirements but Im concerned about having a ssis package that is constantly running.
I was thinking to perhaps have sql agent run the package every 2 minutes and then with the package having precedence constraints to control when each task runs (based upon the time), I'mstill not certain if this is the best approach though.
thanks
April 3, 2012 at 8:52 am
Craig made some good points about why not to have a package running all the time. If you are fine with it and want to proceed, or are bring forced to proceed by requirement, here is something to help you avoid polling via job schedules as well as avoid a messy foreach loop to simulate polling:
If you do not want to use a third-party task you can do the same using the built-in WMI task:
SSIS WMI Event Watcher Check File Exist / Creation
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 9:07 am
opc.three (4/3/2012)
Craig made some good points about why not to have a package running all the time. If you are fine with it and want to proceed, or are bring forced to proceed by requirement, here is something to help you avoid polling via job schedules as well as avoid a messy foreach loop to simulate polling:If you do not want to use a third-party task you can do the same using the built-in WMI task:
Seems like the WMI Task would be suitable, would I need to use predence constraints based on variables to keep it running ie checking for a file(s) every 2 hours.
April 3, 2012 at 9:16 am
You'll have a For Loop Container with a condition of "where 1=1" so it loops indefinitely. In the For Loop Container you'll have the WMI Event Watcher Task waiting for files, and then after that you'll have whatever else you need to process said file or files.
No need to worry about things like "every 2 hours". Per how the WMI Event Watcher Task works, it will wait indefinitely for a file to arrive, all the while using 0 CPU. As soon as a file arrives that satisfies the WMI query your WMI Task will cede to the next Task in the Control Flow, in your case likely a Data Flow Task. Once the Data Flow Task completes the outer For Loop Container will bring you back to the WMI Task which again, will wait indefinitely for another file to arrive.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 9:35 am
Cheers, I think this is the way to go, could you help me with the wql query. The files sit on another server which is a mapped drive(k:\) on the server where the ssis package is located
This the query:
SELECT * FROM __InstanceCreationEvent WITHIN 10
WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and
TargetInstance.GroupComponent= "Win32_Directory.Name=\"k:\\\\Directory1\\\\SSIS\\\\\Universal\\\\FTP_In\\\\NewFilesDirectory\""
there is no parser, so i cant see where the query is wrong, but Im guessing its something to do with the mapped network drive which is (K:\) in my example:
(\\backup)K:\Directory1\SSIS\Universal\FTP_In\NewFilesDirectory\
April 3, 2012 at 9:42 am
Here is a WQL Query Tool for you to develop your queries:
In using a drive letter, are you sure the drive letter will exist for the service account that will be running your SSIS packages on the server? UNC paths have baggage too, but I find them to be infinitely more reliable than mapped drives.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2012 at 9:53 am
opc.three (4/3/2012)
Here is a WQL Query Tool for you to develop your queries:In using a drive letter, are you sure the drive letter will exist for the service account that will be running your SSIS packages on the server? UNC paths have baggage too, but I find them to be infinitely more reliable than mapped drives.
for the moment the package will be running under my windows account which is in the admin group, but eventually it will be running under a dedicated account. Thanks very much for links and help, I very much appreciate it. I'll probably have more questions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply