My clients have an Parts Management application that sits on a SQL
Server 2000 database.

Every month they obtain a list of parts from another division, and
then they run a routine on their database to update it with any new or
changed parts. Currently this is run from within the application in
code.

They want to move this "housekeeping" operation to a SQL Server
scheduled job.

The data update source can be in one of two forms - either a Microsoft
Access database, or CSV text file.

The current import routine allows the user to specify the type of
source file, use a Common Dialog box to navigate to the file, and then
the code runs. Essentially, it extracts the raw data into holding
tables, does a certain amount of data validation and cleanup, and then
inserts or updates to the production tables.

In order for the job to run without human intervention, I envisage a
scenario as follows:

Prior to the job running the SysAdmin must copy the source file to a
dedicated location - for example, a folder named after the month and
year that the job will run.

JOB:

1. Navigate to the correct folder and open the file there - if the
data is in CSV format there will be two files, if it is in Access
there will be one database file containing two tables

2. Run a DTS package to import the data into holding tables

3. Manipulate the data as necessary in Stored Procedures, and then
either use a SPROC or a DTS to move the cleaned data to production
tables.

My questions are these:

1. Does this look like a workable strategy (I've simplified it quite
a bit, but it's essentially what will happen)

2. Can a DTS be made to run conditionally? For example, set up the
Source according to the month and year, so that it will change
depending on the date when it's run?

3. Can you run a job that first of all kicks off a DTS, and then, on
successful completion, executes a series of SPROCs etc., possibly
followed by another DTS?

Thanks

Edward

1. Based on your narrative, it is possible. Creating a specific location for
each month etc. seems an overkill but in general, it seems reasonable.

2. Yes, usually you can use global variables that can be changed or if it
depends on system date, you can change the logic accordingly. Some of the
ideas can be found here: http://www.sqldts.com/234.aspx

3. Yes you can. Each of the DTS executions can be set up as individual steps
within a job and can be set to do whatever actions depending on the success
and failures.

--
Anith