DTS Failing when run as a job

Question

DTS Failing when run as a job

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
April 20, 2004 at 10:29 am

#60097

I have a similar problem to an earlier posting.
I am using DTS Packages to create a large number of Excel spreadsheets. Copying from an Excel template for each type of extract. Using automation to format the spreadsheet, then zipping it. This all takes place on the C:\ drive of my development machine - Windows 2000 Professional.
The extracts run perfectly when I run the DTS package manually but fail when run as a scheduled job.
The Event viewer error message is not very helpful:
SQL Server Scheduled Job 'Extracts' (0x5EE1695417AEE64180AF6894395F0148) - Status: Failed - Invoked on: 2004-04-20 17:02:10 - Message: The job failed. The Job was invoked by User PONLEUR1\artmsss. The last step to run was step 1 (Extracts).
I notice that after running as a scheduled job with a single extract, the Excel spreadsheet is locked for editing. The data has been updated correctly but has not been formatted. This leads me to assume the problem is with the ActiveX code which opens the Excel spreadsheet to format it. (see code below)
Is there a problem using automation from a scheduled job?
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"
'sFileName = "C:\Temp\test.xls"z
Set Excel_Application = CreateObject("Excel.Application")
Set Excel_Workbook = Excel_Application.Workbooks.Open(sFileName)
'Excel_Application.visible = true
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 5 posts - 1 through 4 (of 4 total)

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

Peter P SSCrazy Points: 2777 More actions · Answer 1

The should be no problem so long as the User that the Job is run under has the applicable access rights. Are you user PONLEUR1\artmsss? This user would need Read/Write Access on the C:\ drive of your Dev box.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 2

I had the same problem with one of my developers - the best practice is to develope the DTS packets locally on the SQL server, so they could run with the rights of the acct under which SQL runs. If this is not acceptable, review the permissions on the account under which DTS packet has been created. All the scheduled job are running under the main SQL Server account. Change that and the script will be able to run on a schedule.

MJ

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 3

I don't think this is the problem. The DTS Package was created on the same PC as the Server and the job. Both my id and the account used to start SQL Server and SQL Agent have full access rights.

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

Can you get a more detailed error message by right-clicking the job in EM, selecting "View job history...", check "Show step details" and selecting the step that failed.

This should display the specific error in the message field at the bottom of the dialog. SQLAgent only ever writes that the job failed to event viewer which is never helpful!

Apologies if I'm patronising; if not hopefully this will give you an indication as to exactly what went wrong. But then again it might give you an even more ambiguous message!!