ActiveX code in DTS

[SQLScholar]

Hi,

I dont know whether its better here or with the activeX stuff but i am having a problem with an activeX object (my first ) in DTS.

here is my 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=MSDB;user id = 'myuser';password='mypassword'"

mySQLCmdText = "SELECT  count(*) as [rowcount]  FROM  sysdbmaintplan_history WHERE     (succeeded = 0) AND (DATEDIFF(d, start_time, GETDATE()) = 1)"

myRecordset.Open mySQLCmdText, myConn

If myRecordset.Fields("rowcount").value = 0 then
   	Main = DTSTaskExecResult_Failure
Else
   	Main = DTSTaskExecResult_Success
End If

End Function

But all i get back is a failure saying

"the task reported failure on execution"

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
----------------------------------------

 

[SQLScholar]

ignore it i was being dumb!

----------------------------------------
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
----------------------------------------