Sunday, February 19, 2012

DTS xp..sendmail error

Similar problem was discussed here very recently but that didn't solved my problem.

Inside a DTS package, I have a ActiveX task that checks for a file at some location, on success it should send me a mail using xp..sendmail and on failure it should send me a mail.
The mail task on success works but the mailing task on failure does not work.
Please help.

-RohitPost the activex code.|||here is the ActiveX code.
======================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo
Dim mm
Dim dd

if Len(month(date)) < 2 then
mm = trim("0" & month(date))
else
mm = trim(month(date))
end if

if Len(day(date)) < 2 then
dd = trim("0" & day(date))
else
dd = trim(day(date))
end if

FileName = "Invoice" & mm & dd & year(date) &".txt"
FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value
FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "ATLAS_Invoice.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

End Function|||What version of sql server are you using ?|||Microsoft SQL Server 2000 - 8.00.876 (Intel X86)|||Insert "exit function" after each "Main=DTSTaskExecResult_XXX".|||using "exit function" also was not working, I tried unchecking "Fail package on first error" on the DTS package properties and its now working.

Thanks for your help.|||Yes - that will do it. What made you set that ? Basically, nothing will run after that step has failed.|||I checked it because I wanted the package to stop executing in case of a failure, never knew it wont even run the task that should run with a failure.

No comments:

Post a Comment