Active X Job Pain in Bottom...

• mark richardson-385650

  Newbie

  Points: 7

  More actions

  June 12, 2009 at 2:45 am

  #131755

  Hi,

  I have what appears pretty standard syntax for an Active X Job Step but Job keeps failing on Line 1 ?!

  Any ideas would be most appreciated - Job Below

  Function Main()

  Dim strDir = "D:\MSSQL\Backup\KentFraser\"

  Dim strSearchedExt= "bak" 'extension without the dot

  Dim fs, objDir, objFile

  Dim datMax, objMostRecentFile

  Dim strExt

  set fs = CreateObject ("scripting.filesystemobject")

  set objDir = fs.GetFolder(strDir)

  '~~initialize with a pretty back-dated value

  datMax = CDate(0)

  '~~initialize with empty obj-reference

  Set objMostRecentFile = Nothing

  'browse files for ext and date-max

  for each objFile in objDir.Files

  '~~get extension

  strExt = fs.GetExtensionName (objFile.Name)

  '~~test if extensions match

  if 0 = StrComp(strExt, strSearchedExt, vbTextCompare) Then

  '~~test if datemodified is more recent, i.e. bigger

  if objFile.DateLastModified > datMax Then

  '~~keep matches

  datMax = objFile.DateLastModified

  Set objMostRecentFile = objFile

  end if

  end if

  next

  Set fs = Nothing

  Set objDir = Nothing

  '~~display result

  if not objMostRecentFile is nothing Then

  CopyFilez objMostRecentFile

  else

  'WSH.Echo "no file ending with ""." & strSearchedExt & """ found"

  end if

  Set objMostRecentFile = Nothing

  End Function

  Function CopyFilez(fname)

  Dim sOriginFolder, sDestinationFolder, sFile, oFSO

  Set oFSO = CreateObject("Scripting.FileSystemObject")

  sOriginFolder = "D:\MSSQL\Backup\KentFraser\"

  sDestinationFolder = "D:\FTP_Files\ZipTemp\"

  For Each sFile In oFSO.GetFolder(sOriginFolder).Files

  If Not oFSO.FileExists(sDestinationFolder & "\" & oFSO.GetFileName(sFile)) Then

  If sFile = fname Then

  oFSO.GetFile(sFile).Copy sDestinationFolder & "\" & oFSO.GetFileName(sFile),True

  End If

  End If

  Next

  Set oFSO = Nothing

  Set sOriginFolder = Nothing

  Set sDestinationFolder = Nothing

  End Function

• Mohan.deval

  Say Hey Kid

  Points: 713

  More actions

  January 21, 2011 at 11:18 am

  #1276710

  Yes. I do have a similar issue, the Package runs good in VS2008, ( SQL2008R2)( Debug / Release mode ) , but the same package

  when configured as job complain only about the

  CreateObject("Excel.Application") statement saying cannot create Active-X object....

  I have created a SysAgentProxyAccount having administrative rights to run the SQL Agent.

  Not sure how to provide rights to create an active-x for this account.

  ANother part..... the job is executed sometimes ( like 3 in 10 times when you manually make run the job & fails 7 times.... )

  Searching a lot on this error.

  Thanx in advance,