DTS: Workflow and Send Mail Task

Posted on 2003-02-20
Medium Priority
Last Modified: 2007-12-19

i have several dts packages that are dependent upon each other.  ie detail tables that cannot be populated before master tables.

I have created a "master" package within which to execute these sub packages.  I have inserted the 5 sub packages using the execute package task.
I want to run them as follows

if 1 executes successfully then execute 2.
if 2 executes successfully then execute 3.
if 3 executes successfully then execute 4.
if 4 executes successfully then execute 5.

if any of the packages fail i want the rest of the packages to stop executing - so if package 2 fails i DO NOT want 3, 4, and 5 to execute

so what I have done is put in workflow arrows (on success). between packages: 1-2 2-3 3-4 4-5.  

however when i execute the whole thing: 1. completes successfully, 2 fails BUT then 3 still executes.  Why is this - as i have specified only on success workflow?

so my questions are:
1. how can i prevent all subsequent packages from executing if a previous package fails?

2. also when 1 package fails i would like to send an email to myself.  I have added a send mail task and have set it to execute "on failure". I did this via a workflow arrow between each sub package (1 to 5) and the send mail task.  HOWEVER when sub package 2 fails i DO NOT receive an email.  Yet when i execute the send mail task independently it does send me mail - so it is configured properly.    

3. Finally, is it possible (when sending mail triggered by a  failed execution)- to include details of exactly which package failed and why?

thankyou for your time
Question by:cocosteel
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Expert Comment

ID: 7988690
For point 3. when you right click inside of a dts package and go to the properties page there is a logging option.   If you put a filename in there it logs each step of the package if runs or fails and why.   Then you can just attach the file in your email.  

The workflow should stop the other problem.  I would right click on the workflow arrow and check the properties to make sure all the conditions are in there that you want.

Expert Comment

ID: 7988835
The workflow should work, however, have you considered to create a job that executes each DTS package? It's more easier to email the output from there. And the workflow works always, you can easily indicate to send an email in case of failure, you can view output of each failed step through the history and you don't have to modify a master package and... I think you should really try it.


Author Comment

ID: 7998336
thanks for the info - but exactly how do i get to view the package log and how do i get to attach it to an email automatically.

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more


Author Comment

ID: 7998378
re The workflow should stop the other problem.  I would right click on the workflow arrow and check the properties to make sure all the conditions are in there that you want

when i right click on workflow arrow the only properties show are the very basic precedence options - and i still select on success. but this still allows subsequent packages to execute after the first one has failed?  this doesnt make sense.

Accepted Solution

curtis591 earned 200 total points
ID: 8004015
The error log is just a text file. Notepad will read it fine.  For the sending the email I am guessing that you are using the send mail task?  If so, inside of it of it there a box that is asking for the location of any attachments you want to send along with the email message.  

I have never seen the workflows not work.   I would run the package manually and watch the screen that tells you the which the steps are running and watch carefully at what point each one starts.  I am guessing that step 3 is starting after the completion of step one rather than after the completion of step 2.

Author Comment

ID: 8017211
How to set up correct workflow

1. Go into individual packages and right click on workflow arrow.  Select workflow options
tick fail package on step failure.  This will cause the package to fail if an internal step fails

2. Go to master package that will hold all of the execute package tasks. right click on each execute package tasks and go to workflow properties.  Ensure that the fail package on step failure is not ticked.

3. In the master package go to menu - package - properties - logging tab . Ensure that fail packag on first error is not ticked.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question