Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Email in the DTS script 1

Status
Not open for further replies.
yan19454

yan19454

MIS
May 19, 2005
94
US
I set up the DTS in my server which install smtp mail server.

DTS will try to import the text . Before I import , I want to check whether the file is update otherwise I will email operator to update. if update it, then import.

On the workflow , i added this active script. The mail part is not working. I have cdo install in my computer. I have stored procedure to email . I do not know why. Thx.

Function Main()
Dim oFSO, oConn, sFileName, oFile
' Get the filename from my Text File connection called "Text File (Source)"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Source)")
sFilename = oConn.DataSource
Set oConn = Nothing
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check File Exists first
If Not oFSO.FileExists(sFilename) Then
' SQL Server 2000 Only, log error
' Return Error
EmailNotify("FileNotExist")
Main = DTSTaskExecResult_Failure
Else
' Get file object
Set oFile = oFSO.GetFile(sFilename)

' Check age of file is less than 24 hours
If DateDiff("h", oFile.DateLastModified, Now) >= 160 Then
' SQL Server 2000 Only, log error
EmailNotify("Rob")
' Return Error
Main = DTSTaskExecResult_Failure
Else
' Return Success
Main = DTSTaskExecResult_Success
End If
End If

Set oFile = Nothing
Set oFSO = Nothing


End Function

Function EmailNotify(s)

Dim objMail , strEmailBody, strA

Set objMail = CreateObject("CDONTS.NewMail")

' Set Message Text, including a global variable value
if s="FileNotExist" then

strEmailBody = " \\Dbase-svr\shared\Doc_master\Doc.txt is not existed"

else

'Create the body of the email

strEmailBody = " \\Dbase-svr\shared\Doc_master\Doc.txt is upated"

end if
'objMail.To = "aBC@abc.com"
objMail.Subject = "Doctor Master Update Report"
'Set the Importance to High
objMail.Importance = 2
objMail.Subject = strEmailBody
objMail.Send



' Clean Up
Set objMail = Nothing

End Function





 
Sort by date Sort by votes
Try using SMTP?

Code: 
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

    Dim myMail   

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="DoctorMasterUpdateReport"
    myMail.From="name@domain.com"
    myMail.To= "name@domain.com"
    myMail.TextBody="Enter Your Message Here, " & Chr(13) & Chr(13) _
    & "Make Sure to Delimit Lines" & Chr(13) & Chr(13) _
    & "Thanks," & Chr(13) & "<auto generated message>"
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
    'Name or IP of remote SMTP server
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] _
        =[b]ENTER IP ADDRESS TO SEND FROM HERE[/b]
    'Server port default = 25
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] _
        =25 
    myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing

If ErrorCount <> 0 Then
          Main = DTSTaskExecResult_Success
Else
          Main = DTSTaskExecResult_Failure
End If

End Function

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
EmailNotify()
Main = DTSStepScriptResult_ExecuteTask
End Function



Function EmailNotify()


Dim myMail

Set myMail=CreateObject("CDO.Message")

myMail.Subject="DoctorMasterUpdateReport"
myMail.From="abc@abc.com"
myMail.To= "abc@abc.com"
myMail.TextBody="Doctor Master is not update, " & Chr(13) & Chr(13) _
& "Make Sure to Delimit Lines" & Chr(13) & Chr(13) _
& "Thanks," & Chr(13) & "<auto generated message>"
myMail.Configuration.Fields.Item _
(" 'Name or IP of remote SMTP server
myMail.Configuration.Fields.Item (" ="172.16.11.73"
'Server port default = 25
myMail.Configuration.Fields.Item (" =25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing
end function

I tested it. it gave me
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:ActiveX Scripting encountered a Run Time Error during the execution of the script.
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100

Thanks.
 
Upvote 0 Downvote
I think this is your problem:

Code: 
    Main = DTSStepScriptResult_ExecuteTask

Have you tried using EmailNotify within the context of your original function?

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I suspected my mail have problem. I tested the mail first . it said it failed to the transport failed to connect to the server. I think the ip address is not correct. I ipconfig to get the ip. Thx.
 
Upvote 0 Downvote
I actually use the nslookp set type=mx and key in my server name to get the smtp ip. I got errorsouce=cdo.message.1

I just received email from the server this morning. smtp should work fine. Thx.
 
Upvote 0 Downvote
Might need to check with your IT dept to see if the mailserver is configured for SMTP. If that doesn't work for you, I have a script somewhere that will login and send using SMTP from a gmail account.

Let me know if you have any further problems.

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I receive email this morning which in sQL agent call stored procedure using smtp to mail. I do not know how to convert to active x script.

CREATE PROCEDURE dbo.sp_SMTPemail
(
@To as nvarchar(50)
)
AS

-- Declare
DECLARE @message int
DECLARE @config int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @Body as nvarchar(255)
DECLARE @From as nvarchar(50)
DECLARE @Subject as nvarchar(255)
DECLARE @DVCount as nvarchar(2)

EXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message object
EXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration object

-- Configuration Object
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the network
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'your.server.com' -- SMTP Server
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP Port
EXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoAnonymous' -- Anonymous SMTP Authenticate
EXEC sp_OAMethod @config, 'Fields.Update'
EXEC @DVCount=get_DVCount

set @Subject=' Education Stats'
set @Body='Amelia, please forward the email to . Today is ' +convert(nvarchar(20),getdate())+' Holy Report: ' +'Total Count for Last Month is: '+convert(nvarchar(20),@DVCount)

-- Message Object
EXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = config
EXEC @hr = sp_OASetProperty @message, 'To', @To
EXEC @hr = sp_OASetProperty @message, 'From', 'abc@abc.com'

EXEC @hr = sp_OASetProperty @message, 'Cc', 'abc@abc.com'
EXEC @hr = sp_OASetProperty @message, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @message, 'TextBody', @Body
EXEC sp_OAMethod @message, 'Send()'

-- Destroys the objects
EXEC @hr = sp_OADestroy @message
EXEC @hr = sp_OADestroy @config

-- Errorhandler
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
GO
 
Upvote 0 Downvote
Alex:

I copied your code and created the active x script changed to my ip and mailfrom and mailto to my email and run, I still got the same error


Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:ActiveX Scripting encountered a Run Time Error during the execution of the script.
Step Error code: 800403FE
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
 
Upvote 0 Downvote
I think that you want to set up your script so that EmailNotify will return something to Main, indicating success. Have you tried something like this:

Code: 
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
    dim MyEmail 

    MyEmail = EmailNotify()

[b]If MyEmail = True Then
          Main = DTSTaskExecResult_Success
Else
          Main = DTSTaskExecResult_Failure
End If[/b]

End Function



Function   EmailNotify()


        Dim myMail   

    Set myMail=CreateObject("CDO.Message")

    myMail.Subject="DoctorMasterUpdateReport"
    myMail.From="abc@abc.com"
    myMail.To= "abc@abc.com"
    myMail.TextBody="Doctor Master is not update, " & Chr(13) & Chr(13) _
    & "Make Sure to Delimit Lines" & Chr(13) & Chr(13) _
    & "Thanks," & Chr(13) & "<auto generated message>"
    myMail.Configuration.Fields.Item _
        ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")=2[/URL]
    'Name or IP of remote SMTP server
    myMail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL]  ="172.16.11.73"
    'Server port default = 25
    myMail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL]  =25 
    myMail.Configuration.Fields.Update
    myMail.Send
    set myMail=nothing

     [b]If ErrorCount <> 0 Then
          EmailNotify = False
        Else
          EmailNotify = True
     End If[/b]

end function

Also, I think that in the future you will find it handy to use [ignore]
Code:
[/ignore] tags around your long code blocks. THey make it much easier to read.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
I dug my old application and found the ip address, that is the our exchange server and tried that, it work. Thx. I do not understand why my smtp did not work. I have the issue before and told to our network guys , they did not know.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekpr00
  • Locked
  • Question
Help with concatenation of BIG integers
Replies
0
Views
163
tekpr00
tekpr00
ItHurtsWhenIThink
  • Locked
  • Question
Using Distinct and count in same query???
Replies
4
Views
205
gk53
gk53
botatun
  • Locked
  • Question
the linked server insert data to csv in wrong order
Replies
0
Views
155
botatun
botatun
Fsoumia
  • Locked
  • Question
DTS Logging
Replies
2
Views
174
Fsoumia
Fsoumia
Imakeoil
  • Locked
  • Question
Used DTS Migration Wizard but can't find the DTS Packs
Replies
1
Views
90
drlex
drlex

Part and Inventory Search

Sponsor

Back
Top