I have a DTS step that on failure sends an email through
an ActiveX script. I do not want the package to indicate
that it has failed though. How can I set the execution
status of the package to be successful and not failure??

TIA,
Scott Lindsey

In article <1f4701c3a23b$a1cddfe0$3101280a (AT) phx (DOT) gbl>, Scott Lindsey
<sflindsey (AT) highlights-corp (DOT) com> writes
You can't. Best bet is to prevent the failure before it happens.

Depends exactly what the failure but often workflow is a nice solution
to preventing a task from running, or skipping a task that your know
will fail.

e.g.

How can I check if a file exists?
(http://www.sqldts.com/default.aspx?211)

Skip the execution of an individual Task
(http://www.sqldts.com/default.aspx?214)
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,
Thanks for the quick response!! This is what I was afraid
of because I was unable to find anything on your website
and anywhere else.

Scott

Hi Scott,

I would like to thank Darren for his help. I agree with him that your
requirements are hard to meet. If you would, you may tell us the root cause
that you do not want the package to indicate that it has failed though.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Michael,
The DTS package runs 4X a day. Sometimes the file can be
empty. The Data Driven Query task that I used to load a
SQL table with the information from the file does not like
a file with a file size of zero. I added a step before
the DDQ task to check to see if the file size is zero. If
the file size is zero, then I want the job to still appear
as successful even though there are no records to
process. The ActiveX script task that checks the file
size has a success path that goes to the DDQ task and a
failure path that goes to another ActiveX script task that
send an email indicating that the input file was empty.
The job has an email notification setup to email me when
the job fails. Since I fail the ActiveX script task that
checks the file size, SQL Server fails the job and I get
an email notification through SQL Server Agent indicating
the job has failed.

Scott

Hi Scott,

Thanks for your feedback. I start to understand what you are doing. However
I still think that it is not necessary to set the job to appear as
successful. If the job appeared failed, the job can still run continuously
by job schedules. Do you have any further concerns?

If you consist on doing that, I think the following article will help you a
lot. But I think it is a heavy work and unworthy.
Multiple Paths in Workflow
http://www.sqldts.com/default.aspx?218,1

This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

In article <047d01c3a2d3$b43558a0$a001280a (AT) phx (DOT) gbl>, Scott Lindsey
<sflindsey (AT) highlights-corp (DOT) com> writes
As Michael suggest the Multiple Paths in Workflow method is too much
like hard work here. I would use a single ActiveX Script Task to read
the size of the file and store the result in a global variable.

Then have two On Success constraint leading from that task, one to the
DDQ, and one two the second ActiveX Script Task that sends the email.

Use Workflow ActiveX Script on each of the two successive tasks to check
the size and decide if it should execute.

e.g.

On the DDQ you would use something like this-

Option Explicit

Function Main()
If DTSGlobalVariables("FileSize").Value > 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End Function


and on the Email task use this-

Option Explicit

Function Main()
If DTSGlobalVariables("FileSize").Value = 0 Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If
End Function


Some code that demonstrates using the FSO to get the file size can be
found here-
Working with files and the FileSystemObject
(http://www.sqldts.com/default.aspx?292)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren and Michael,
Thanks so much for the detailed information!!!

This has solved my problem.

Thanks again,
Scott Lindsey