Newbie questions about workflow based upon response from user, restarting the package

Newbie questions about workflow based upon response from user, restarting the package

Post by Colin Coli » Thu, 10 Apr 2003 04:52:00

I am new to DTS but so far I like what I see.

We currently do monthly imports of allot of data into SQL that Access
drives.  I want to get rid of the Access front end and do it all with
DTS.  The data is coming from Text files.

There are about 30 files.  So I wanted to track the status of each file.
So if something happens and we have to restart, I don't want it to start
all over.

So I made a Job table and a JobStatus Table.  The Job Table will just
contain a JobID, Start & end Date.  Basically for each batch.  The
JobStatus table will contain each step (which will be each package)
within the whole process.

So once I start a "Job", it will go through all of the packages.  But
again if something happens, I would like to continue the job where it
left off.

I am going to have a parent package that will execute all of the other
packages.  I figured in the parent package, is where I would initialize
the Job record and a JobID.  

Then each child package will see if it has been done for the JobID that
is a global variable from the Parent package.  If it has then it won't

So my first questions have to do with initializing or continuing a JobID
in the parent package.

Here is how I am going about it so far. I have an Execute SQL Task that
basically queries my job table for a job without an end date.  I assign
the output to a global variable defined in the Output Parameters.
In my next step I have an ActiveX Script that looks at the global
variable.  If it is present then I have a message box to the user saying
"JobID 1 Started on 03/03/2003 is still open, do you wish to continue
this Job?".  And the user can click Yes, No or cancel.

So what I WANT to do (this is the part that I need advice in ) is if the
User clicks Cancel then end the whole thing right there.

If the user clicks No then it will ask: "Are you sure you wish to Close
JobID 1?"
        IF the user clicks No then end the whole thing
        if the user clicks Yes then set an end date to the record and Start
over at the very beginning

And if the User Clicks yes for the first question which was "JobID 1
Started on 03/03/2003 is still open, do you wish to continue this Job?",
then it will just start executing the child packages.

Now if there are no jobs present, then it will create new Job record,
set the Global JobID variable and execute the child packages.

I am basically wondering the best way to do what I described above,
starting with my ActiveX script.

I would also like to know is if I am initializing my new Job, what is
the best way to add a record to the Job table then set the JobID global
variable the value of the JobID in my new record? (it is an
autoincrement field)

I will have other questions pertaining to this whole thing but I figured
this is a good place to start.


*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!


1. Newbie question--Duplicate data based upon primary keys?


I am a newbie to SQL server 6.5.

I will need to stress test a sql server 6.5  test database by duplicating
data. To do so, I need to know how to modify the primary key(which are
numbers in character data type) to duplicate the data several times over.
Does any one have a script I can run to do so?

Can I do this sql or do I need some sort of stored procedure?  Any help
would be greatly appreciated. THanks.

David Spaisman

2. Coloring Grid row text (revisited)

3. Gathering data based upon user input of date

4. printing Multiple field on many pages

5. Upon completion workflow not executed on Templog full

6. using RDO to get a list of Data sources from sql server 6.5 problem. any help is greatly appreciated.

7. Automatically opening files upon restarting FileMaker Pro

8. SQL Loader and Personal Oracle - Please Help

9. Database recovery upon System restart

10. Newbie question: improve VB front-end app response time

11. Oracle application response time degrades until listner is restarted

12. selection based upon time differances.

13. Can a graph series be based upon a calculated function another series