Hi

I want to create a DTS package that does a one-off bulk import of data into
a database.

It needs to run this bulk import within 10 minutes of the data-files
appearing in a directory; these datafiles will be generated by an
"un-predictable" automated process - by that, I mean the files could appear
at any time, not a predictable time.

To achieve this, I thought that I could schedule the package to run every 10
minutes.

If the DTS package does not find any files then it completes and waits until
the scheduler restarts it 10 minutes later.

However, if the DTS package does find the files, then I want it to import
the data and effectively disable the scheduler. I don't want the scheduler
to run the package if the files have been found. This disabling of the
scheduler would have to occur before the data import has effectively
started...if the data import takes more than 10 minutes then I don't want
the DTS package running in two (or more) instances.

I'm not sure how to go about this. I can't find a way of getting the DTS
package to disable it's own schedule. The other way I though of doing this
was to have a global variable within the DTS package called "RUN" that was a
boolean value. If the value was TRUE then the package would run and if it
was FALSE then the package wouldn't run. As soon as the DTS package had
verified that the data files were there then it would have to set the value
of the global value to FALSE. However, this would have to be durable data,
so the DTS package would immediately have to save it self to make this
"FALSE" value permanent so that subsequent runs of the DTS package realised
that the files had been found in a previous run. I certainly wouldn't want
the package to be run again, particularly if an existing import was still
taking place. However, I don't know how to make a DTS package save itself
during execution.

Is what I'm trying to achieve possible? If so, what's the best way to go
about it? I realise that I could simply rename or move the data import
files, so that the files effectively aren't there, but this isn't "perfect".

Many thanks in advance

Griff

OK

What you can do is

Build a package to import the files. First step in the package is to check
the file count in the directory. If there are < the total amount then exit
gracefully using workflow. The very last step of the job, when the job has
run is to execute Execute msdb.dbo.sp_Update_Job. You need to pass in the
job name and the enabled property setting it to false
Schedule the job to execute every 10 mins

If you then want to do the same the following day you have a seperate job
that simply re-enables the job first thing

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"GriffithsJ" <jonathan.griffiths (AT) ipuk (DOT) com> wrote

Here it is from BOL, under Administering SQL Server:

Note Only one instance of the job can be run at a time. If you execute a job manually while it is running as scheduled, SQL Server Agent refuses the request.

Only one instance of a job can run at any one time. I know it says "manually", but I have a similar situation where I run a job whenever the CPU is idle, and it never "double runs" even if I set the idle level high enough where it could.

---

Regarding your original question, Griff, another approach, rather than turning the job off, is to just let the job run every ten minutes. As you said, if it finds no files, no problem; it'll check again in ten minutes. Then, when it does find the files, it processes them, but during this processing time you don't have to worry that the scheduled job will run again because it can't. Then, at the end of the job, use a step to move or wipe out the files that are in that directory (you can do this via batch file commands or by using ActiveX file objects). Now the job will continue to check every ten minutes, but will find nothing and so exit gracefully.

Best regards!




"J O Holloway" <jholloway (AT) pinncorp (DOT) com> wrote

A job instance will not execute again at the same time as it is running from
it's previous instatiation.

When I said in my explanation ....the very last step of the job that should
have been ...the very last step of the package i.e. after the imports.

--


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"J O Holloway" <jholloway (AT) pinncorp (DOT) com> wrote