DTS Package fails when scheduled but runs when manually run

Question

Post reply

DTS Package fails when scheduled but runs when manually run

Viewing 11 posts - 16 through 25 (of 25 total)

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

Isabella Sokolovksaya-426655 Grasshopper Points: 19 More actions · Answer 1

May be it would help:

Check your job for the following:

On General Tab have checkbox Enabled - checked

On Schedule Tab, click button Edit:

- have checkbox Enabled – checked

- Make sure the schedule type is correct and time has not passed yet.

Isabella

Gabriel Andres Brancolini SSC Rookie Points: 46 More actions · Answer 2

Try this:

Go to Service Mannager at the server, and look for SQL Server Analysis Services, then look for properties window and go to the Session tab (i have it in spanish as "iniciar sesion", probably in english you should look for Security tab), and then select the option where you set a custom domain account. Remember that this account should never expires and will be used ONLY for this kind of service, not for other porpouses. What this do is to log as a person with some rights and jobs will run with this credentials. For security reasons this should be configured by your network administrator.

Have a nice day!!

Gabriel

Gabriel Andres Brancolini SSC Rookie Points: 46 More actions · Answer 3

Try this:

Go to Service Mannager at the server, and look for SQL Server Analysis Services, then look for properties window and go to the Session tab (i have it in spanish as "iniciar sesion", probably in english you should look for Security tab), and then select the option where you set a custom domain account. Remember that this account should never expires and will be used ONLY for this kind of service, not for other porpouses. What this do is to log as a person with some rights and jobs will run with this credentials. For security reasons this should be configured by your network administrator.

Have a nice day!!

Gabriel

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

The agent usually runs under a different user tham SQLSVC. You can check this by right clicking on the agent. Make sure the account it is running under has the correct permissions. Also set the job to create an error log so you can get more detail on the failure.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 5

If you are running a dts that involves more than 1 machine, is AMERICA\SVCSQL and admin on all machines?

ksmith0826 Grasshopper Points: 17 More actions · Answer 6

Anyone know how this issue was resolved?

Thanks in advance

--Kent

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

it usually comes down to permissions on the agent account or package owner account or if the issue is that it runs manully from a client but does not run as a job on the server that there are differnt drivers installed on the machines. What is the exact issue you are having and have you looked at the logs?

jspicer Newbie Points: 9 More actions · Answer 8

Did anyone get answers on this post? I am having similar issues, but am new to DTS so not sure how to resolve. I get this error:

Executed as user: MAGLOG\Administrator. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [ProvideX][ODBC Driver][FILEIO]Table is not accessible Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 12 (C) Error string: [ProvideX][ODBC Driver][FILEIO]Table is not accessible Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSSendMailTask_2 DTSRun OnFinish: DTSStep_DTSSendMailTask_2 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

The package runs fine manually executed, it fails when scheduled.....

asfazrq Newbie Points: 9 More actions · Answer 9

I was able to fix the issue by:

1. Scheduling the task.

2. get the step definition of the task (i.e. DTSRun ~/2312312123123123123123)

3. Create a batch file with the DTS package runtime definition

4. Scheduling the batch file as a Windows Scheduled Task instead of SQLServerAgent task.

Hope this helps.

Regards,

Asfaz Qazi

yizhe2000 Valued Member Points: 65 More actions · Answer 10

I have fixed the probloem.

change the Account that START the Agent Service .the defalt user is system.

I change to administrator.

Change the owner of the job to administrator too.

then fixed.

gangstead Newbie Points: 7 More actions · Answer 11

I had a similar problem and it was resolved by changing the permissions of the SQL login. There is a windows user that SQL Agent runs under and there is a SQL login with that same name.

The big tipoff in my case was in the job history. Schedule it to run once and then run it manually once. I found no errors for the job or its one step in either case. The job step history message would say "executed as user: domain\agent_account ..." for both the manual and scheduled execution, again without error.

The key was in the message for the job:

Scheduled job history message would begin: "The job succeeded. The job was invoked by Schedule 102 (schedule name)...."

Manually started job history message begins: "The job succeeded. The job was invoked by User sa ..."

Schedule ## (name) means it's the SQL Agent's MSSQL account.

Go up to security -> logins and compare sa to the account for the SQL Agent. In my case I had to add a user mapping and db_owner role. When the job ran via schedule it wasn't able to insert rows into the desired database, my SSIS package was fine (SSIS is SQL 2k8 speak for DTS).