Occasional error in my job

Question

Occasional error in my job

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
March 1, 2005 at 10:34 am

#61662

I run a job overnight everynight on both Production server and development machine.
It never fails on the Production server but occasionally it fails on the development machine.
Error:
DTSRun OnStart: DTSStep_DTSActiveScriptTask_5   DTSRun OnError: DTSStep_DTSActiveScriptTask_5, Error = -2147220482 (800403FE)      Error string: Error Code: 0   Error Source= Microsoft VBScript runtime error   Error... Process Exit Code 1. The step failed.
Any suggestions as to why the following script would occassionally cause errors but most of the time work OK
Any improvements I could make to the script to eliminate problem or at least identify the cause.
Function Main()
Dim Excel_Application
Dim Excel_Workbook
Dim Excel_Worksheet
Dim sFileName
Dim sSheetName
Dim sSecondLineHeader
Dim iSheetCounter
sFileName = DTSGlobalVariables("TemplatePath").Value + DTSGlobalVariables("OutputName").Value + ".xls"
Set Excel_Application = CreateObject("Excel.Application")
Set Excel_Workbook = Excel_Application.Workbooks.Open(sFileName)

Select Case DTSGlobalVariables("OutputType").Value
  Case "US"
   sSecondLineHeader = "User: "
  Case "CT"
   sSecondLineHeader = "Country: "
  Case "CL"
   sSecondLineHeader = "Cluster: "
  Case "RE"
   sSecondLineHeader = "Region: "
End Select
sSecondLineHeader = sSecondLineHeader + DTSGlobalVariables("OutputFilter").Value
for iSheetCounter = 1 to Excel_application.sheets.count
        With Excel_Application.sheets(iSheetCounter).PageSetup
           .LeftHeader = "&B" + "KnowledgeBase"
           .CenterHeader = "&B" + DTSGlobalVariables("OutputName").Value + chr(10) + sSecondLineHeader
            .RightHeader = "&B" + "Extracted: " + formatdatetime(now, 1) + " " + formatdatetime(now, 4)
        End With
next
Excel_Workbook.Save
Excel_Workbook.Close
Set Excel_Worksheet = Nothing
Set Excel_Workbook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing Main = DTSTaskExecResult_Success
End Function

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Can't see anything wrong with your coding at all.

However , 'sSecondLineHeader = sSecondLineHeader ' could be causing a problem. I have encountered problems before when trying to re-assign a local variable with a value already assigned.

I would also only use these lines of code to destroy open objects etc.

Excel_Workbook.Save

Excel_Application.Quit

Set Excel_Application = Nothing

There is also the possibility that the file is already open when you run your code, which will cause error.

------------------------------
The Users are always right - when I'm not wrong!

marinav SSC Enthusiast Points: 136 More actions · Answer 2

You may check your Windows backup schedule if it doesn't collide with this job. I had something similar when file was momentarily locked for archive right before used in DTS.