SSIS package fails when run as a job, success when run manually

Question

SSIS package fails when run as a job, success when run manually

danielmanke

SSC Eights!

Points: 824
More actions
February 23, 2010 at 9:31 am

#219433

Package stops a service, creates a dir, copies some files, starts the service. The job is run and owned by the Agent service account. Runs manually as any number of users, both Domain admin and service account. Fails with both users listed when run as a job.
Code that fails is in a scipt task as follows:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ServiceProcess
Public Class ScriptMain
Public Sub Main()
'stop ReportServer service script task
Dim controller As New ServiceController
controller.MachineName = "."
controller.ServiceName = "SERVICENAME" --This step fails when SSIS is run as a job
Dim status As String = controller.Status.ToString
If status.ToLower = "running" Then
controller.Stop()
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Any help would be appreciated:

Viewing 14 posts - 1 through 13 (of 13 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the error returned? My guess is that you need to create proxy using a user that has rights to start and stop services and then runt he job step using that proxy.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

danielmanke SSC Eights! Points: 824 More actions · Answer 2

Error is as follows:

Date2/24/2010 11:47:21 AM

LogJob History (JOBNAME)

Step ID1

ServerSERVERNAME

Job NameJOBNAME

Step NameRun SSIS

Duration00:00:02

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: DOMAIN\USERNAME. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:47:21 AM Error: 2010-02-24 11:47:22.90 Code: 0x00000002 Source: Stop Service Description: The script threw an exception: Cannot open <SERVICENAME> service on computer '.'. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:47:21 AM Finished: 11:47:22 AM Elapsed: 1.015 seconds. The package execution failed. The step failed.

I can run the SSIS manually using the same user as above and it runs...

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Although I know "." equals local machine, I have seen weirdness.. You might try actually using the machine name and see if it makes ANY difference..

CEWII

danielmanke SSC Eights! Points: 824 More actions · Answer 4

Just to be clear, this ssis package fails ONLY when run as a job. The SSIS and all code runs well when it is started manually in Visual Studio.

DAM

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Still looks like a permissions issue to me. Works in vs because it is running using your privileges. In a job it is running under the agent account which doesn't have service control rights. This is why you need a proxy.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Elliott Whitlow

SSC Guru

Points: 102402

February 24, 2010 at 12:41 pm

#1124512

Jack makes an excellent point.

CEWII

danielmanke SSC Eights! Points: 824 More actions · Answer 7

I have logged onto the server as the agent account ( a domain account) and the ssis runs fine when run manually. We have also set this agent account as a local admin and still no luck.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Help me by being paranoid for a second..

Execute this command:

EXEC master.dbo.xp_cmdshell 'SET'

It will return all the environment variables available to the SQL Server.

I'm curious what the line that says: USERNAME=

has in it.

If you don't see it then SQL is running as localsystem, it is also reflective of that if you see this line:

USERPROFILE=C:\Documents and Settings\Default User

What I'm really trying to do is make sure that SQL and agent are running as the user you think.. I admit it is grasping but then we know for sure..

CEWII

danielmanke SSC Eights! Points: 824 More actions · Answer 9

results of the xp_cmdshell query for USERNAME = svc_sqldev and USERPROFILE = C:\Documents and Settings\svc_sqldev which is as expected.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

If you execute a NET STOP command on the service through xp_cmdshell does it stop?

CEWII

danielmanke SSC Eights! Points: 824 More actions · Answer 11

Yes, I can use NET STOP and NET START to stop and start the service in question...

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Ok, you got me.. Stumped.. I'm absolutely sure it is something simple, something stupid.

CEWII

danielmanke SSC Eights! Points: 824 More actions · Answer 13

I finally got it to work. The only change I made was to change the owner of the sql job to the service account. That seemed to do it... At this point I'm not sure how many different configurations I tested, I thought I had tested that previous, but it is working now. Thanks to all who responded.

DAM