by 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
execute.
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.
Thanks
-Colin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!