Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Question Unanswered: 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.

    -Rohit

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Post the activex code.

  3. #3
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    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

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What version of sql server are you using ?

  5. #5
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    Microsoft SQL Server 2000 - 8.00.876 (Intel X86)

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Insert "exit function" after each "Main=DTSTaskExecResult_XXX".

  7. #7
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Thumbs up

    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.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Yes - that will do it. What made you set that ? Basically, nothing will run after that step has failed.

  9. #9
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •