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
  • 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.

Industry Leaders: 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!


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

Receive 1:1 tech help

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

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

621 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