Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

Question regarding designing ETL process with DTS

    We have two ideas based on how a DTS package should be built for an ETL process. One methodolgy is to seperate it into multiple packages and then run them utilizing jobs. The other methodology is to create the process in one master package and run the single package as a job.
    Based on experience, what is the more logical design. If you answer, please do not choose A or B, identify which one is the best way and give supporting reasons (pro's and con's) on why one is more efficient than the other.
    Once again, thank you for your ongoing support!
0
Kevin_Maillet
Asked:
Kevin_Maillet
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
randy_knightCommented:
With DTS, I always did it like your option A for the following reasons:

1. The DTS ExecutePackage task is a pain to work with.
2. It is much easier to restart a failed job than to go into your master package, disable steps that have al ready completed, etc.  
3. If you build some metadata tables to handle pre-requisites and complettion of batches, etc. you can go massively parallel this way.

I coud go on and on.  SSIS is a different world but with DTS this is the only wayt to do it IMO.

0
 
Anthony PerkinsCommented:
It really depends if you are passing global variables from the parent to the child and back.  If the answer is yes, than you have no choice
0
 
Kevin_MailletAuthor Commented:
Is there any performance reasons why one is more effective than the other?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
You should not be focusing on performance as the key reason.  If there diffrences, they would have to be negligable.  Instead focus on maintenance and if the steps are independent of each other.  If they are than go with using jobs, if not than use a master/child DTS package approach.  That is the correct lithmus test to make.
0
 
Mark WillsTopic AdvisorCommented:
Definitely look towards operability and scalability. I do like a modular approach, with each module being it's own package, and also self contained. For that reason, often see a few packages being brought into an ETL process, but in such a way that they can largely run independantly of the previous package.

For example, load staging, validate staging, report exceptions, put away staging. Can be four distinct packages - each has a specific job to do, and works with whatever data may exist (or even does not exist because of a failure). The schedular is essentially the glue that co-ordinates the packages - and that is often where there is a sometimes mistaken belief that they all need to know about each other. Certainly there is no point validating data that didn't load, but then, the validation doesn't have to do anything anyway.

Some purists will toally disagree, but, the basis for my reasoning is, there is very little performance overhead in running a task that has nothing to do, and maximum flexibility within a discrete module. I can change validation package as much or as little as needed without impact on anything else. More so, I can farm out different jobs to different developers based on skills, exeperience, and knowledge of data.

So take an object oriented perspective of the ETL process and manage each object (package). It kind of flows on from acperkins statement "Instead focus on maintenance and if the steps are independent of each other" except I take it one step further and suggest make the steps independant. It also kind of fits in with rand_knights suggestions, in so much as each individual step can be isolated, worked on etc...

You do have to design it pretty well, and in the validate process flag errors, and or approved data so that any ensuing steps can take appropriate action. ETL is data driven, so let the data drive the process.
0
 
Kevin_MailletAuthor Commented:
Thank you for your help gentlemen
0
 
Mark WillsTopic AdvisorCommented:
A pleasure. What did you decide to do ?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now