February 16, 2009 at 7:48 am
I have a job running a dtsx.
With Visual studio it s running fine, as job on sql 2005 it produce errors like this:
Date16/02/2009 15:39:46
LogJob History (evfaformation)
Step ID1
ServerEUFRPARSQL1
Job Nameevfaformation
Step Name1
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: EU\sauve. The package execution failed. The step failed.
[ scripted the job:
USE [msdb]
GO
/****** Object: Job [evfaformation] Script Date: 02/13/2009 16:38:42 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [DTS scheduling] Script Date: 02/13/2009 16:38:42 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DTS scheduling' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DTS scheduling'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'evfaformation',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'DTS scheduling',
@owner_login_name=N'EU\sauve', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 02/13/2009 16:38:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/FILE "\\eufrparsql1\DTS\evfa\EVFA 2008 Export des formations.dtsx" /DECRYPT evfa /CONNECTION BDD;"Data Source=EUFRPARSQL1;User ID=evfa;Initial Catalog=Evfa2008;Provider=SQLOLEDB.1;Persist Security Info=True;" /CONNECTION Formations;"Data Source=\\FS00\IT\R&D_Projets\Entretiens annuels 2008\Technique\Docs\09-02-09 demandes de formations 2008.xls;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'lundi',
@enabled=1,
@freq_type=8,
@freq_interval=2,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20090209,
@active_end_date=99991231,
@active_start_time=70000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
I dont knxo what it could be , sure is that since we moved to sql 2005 all our job based on DTSX are very hard to be executed
February 16, 2009 at 8:24 am
Hello,
Assuming that you have other DTS Packages that work Okay on the SQL Server and the fact that you mentioned the Package works fine when run from Visual Studio, then I would guess that it is related to permissions of the Account under which the SQL Server Agent Service runs.
An example would be that the SQL Server Agent Account Service does not have permissions on the file \\FS00\IT\R&D_Projets\Entretiens annuels 2008\Technique\Docs\09-02-09 demandes de formations 2008.xls
One way to test this theory out is to temporarily change the SQL Server Agent Service to run under the same account that you used when you successfully executed the Package from Visual Studio.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
February 16, 2009 at 8:36 am
the account that is used for running the service is an Admin domain account
February 24, 2009 at 5:59 pm
Hello again,
Apologies for the delayed response, but I have had a few days holiday.
When you run the failing job with verbose Logging turned on, what is the full error text that you get?
Regards,
John Marsh
PS: This article explains how to turn on logging: http://www.mssqltips.com/tip.asp?tip=1411
www.sql.lu
SQL Server Luxembourg User Group
February 25, 2009 at 12:54 am
Hello
i did what the article explain but there are no such options like the article describes.
I attached a screenshot.
February 25, 2009 at 3:33 pm
Hello,
Are you a member of the sysadmin role on the SQL Server? I suspect you can not see or amend the Step Properties because you do not have a high enough permission level. (Please see the BOL Topic “SQL Server Agent Fixed Database Roles”).
If this is the case can you ask a member of the sysadmin role to (temporarily) turn on verbose logging?
Without the full error message it will be (very) hard to determine the root cause of your issue.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
February 26, 2009 at 12:15 am
Hello
i am SA
February 26, 2009 at 5:11 pm
Hello,
Do you have SSIS Logging configured for the Job Step? Any useful diagnostics captured there that you could post?
Alternatively, just for bug hunting you could convert the Job Step to a CmdExec that then calls DTEXEC.exe to execute the SSIS Package. Again best to turn on verbose logging.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply