ActiveX to not send file

[SQLScholar]

Hey all,

I have the below code

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()


dim myConn
dim myRecordset

set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=IMS;user id = 'user';password='pwd'"

mySQLCmdText = "select count(*) as [rowcount] from diary where datediff(d,[input date],getdate()) = 0"

myRecordset.Open mySQLCmdText, myConn

If myRecordset.Fields("rowcount").value = 0 then
 	Main = DTSStepScriptResult_DontExecuteTask

Else
   	Main = DTSStepScriptResult_ExecuteTask

End If

End Function

This is on the sendmail task - on the activeX workflow.

Unfortunately becuase there is no sucess link between the file being written to disk (on a destination text file) and the sendmail - sometimes the sendmail fires before the file has been written. How do i sort this out?

Basically i want to not send the email if there is nothing in the report.

Any ideas?

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[AlexCuse]

I would have just one task, and have it run on success of the previous task. Something like this:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()


dim myConn
dim myRecordset

set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")

myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=IMS;user id = 'user';password='pwd'"

mySQLCmdText = "select count(*) as [rowcount] from diary where datediff(d,[input date],getdate()) = 0"

myRecordset.Open mySQLCmdText, myConn

[b]If myRecordset.Fields("rowcount").value > 0 then
     'call your mail function
     'if record count is 0, no mail sent
     myMailFunction
End If[/b]

[b]Main = DTSTaskExecResult_Success[/b]

End Function

If you need an activeX script for sending email (as opposed to a sendmail task), search this forum I know a few have been posted. I also like to return a boolean from the mail function so I can tell if it sent or not.

Hope this helps,

Alex

[small]----signature below----[/small]
You can't fit a square data in a round table