Which Approach? Job Steps or DTS Tasks

Question

Which Approach? Job Steps or DTS Tasks

dean gross

SSCrazy

Points: 2632
More actions
April 14, 2004 at 6:50 am

#107961

Should I create a job that runs a large DTS package with many tasks or create a job with many steps in which each step runs a small DTS package with a few tasks. What are the pros\cons of each approach?
TIA
Dean

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

I think I would go down the multiple job steps route. If any of the jobs fails, you'll be able to identify more quickly exactly where the process is going wrong (view job history / show step details) and then fix the less-complex DTS package more easily.

I don't know about performance considerations though.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 2

I prefer fewer larger ones, though I do separate out smaller ones that could be independently used for other things. For example, if I have an import that triggers a subsequent export, I will use two separate packages for that normally.

This is definitely a fuzzy subject, one for which there are many good answers. Still, I don't want to have too many DTS packages where fewer would be fine, and I find that the error output logs can quickly get me to the step I need regardless of the size of the package.

ChrisMoix SSCommitted Points: 1568 More actions · Answer 3

I have some pretty detailed DTS jobs that import data from our production financial system to a reporting database (as well as backup production, restore to a test server, do some data cleanup,etc). I have about eighteen 'steps' that this nightly process goes through. Each step is a seperate DTS packages (each made up of about 8 transformations). I then have a master DTS package that executes these sub-packages in the correct order, etc.

I put in some custom logging to a table to a table, so I can track when each transformation starts, stops, success, etc. I have a one step SQL Server Agent job that starts the master DTS package.

ChrisMoix SSCommitted Points: 1568 More actions · Answer 4

the last paragraph of my reply got cut off...

I like this method because I can do some custom reporting for management /interested users. I prefer having one SQL Server Agent job that executes a master DTS package rather than an Agent job that has a lof of steps that each execute a single DTS package. It is a lot easier to control work flow in DTS, and it is a lot easier to make changes to the DTS package than to try to control all of those 'On success goto step 27, on failure go to step 32' settings in the advanced tab on the Agent job steps.

Hope this helps.