The package execution returned DTSER_FAILURE (1)

Question

The package execution returned DTSER_FAILURE (1)

shalabh.sachdeva

SSC-Addicted

Points: 422
More actions
June 10, 2010 at 3:09 am

#233627

There is a sql job in Sql Server2005 which invokes a Stored Procedure to call SSIS package. This package contains a step where at remote server a windows schedule task invokes a batch file.
The error that I receive is inside the sql job log file
Description: In Executing "C:\WINDOWS\system32\schtasks.exe" "/Run /S \\chi-nt-damlais1 /TN "bat_file_test"" at "C:\WINDOWS\system32", The process exit code was "1" while the expected was "0".
Also, DTExec: The package execution returned DTSER_FAILURE (1). at the end of the logs.
/run /s \\chi-nt-damlais1 /tn "bat_file_test" is what is been passed as an argument inside the SSIS step to invoke the windows schedular at remote server.
One more thing, when I execute the SSIS from Visual basic alone, there is no problem atall. The problem is only when sql job calls this SSIS it ends up successful but without invoking the bat_file_test windows schedular at remote server.
Hope I make you understand what the problem is..
Can you help. Thanks.
Shael~

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

90% of the time that a SSIS package works in BIDS but it doesn't in a SQL Server job, it has something to do with permissions. So make sure that the account the SQL Server Agent runs under has the necessary permissions to execute every single step of your package.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

I completely agree, it is VERY often permissions. And absolutely the first place to start..

CEWII

shalabh.sachdeva SSC-Addicted Points: 422 More actions · Answer 3

I have never messed up with the permissions under SQL Agent, can you tell me how to check these and change them in order to work this issue.

Shael~

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

There are a couple things in play. Does your package have embedded passwords in connection strings? If so, unless the package is executed as the user who entered those connection strings the passwords will not be available at run-time. This usually leads to package configurations or changing connection strings in SQL agent jobs. Then the question is what user does the SQL Agent run as and what permissions does it have on all source and destination locations. You can find out what user SQL agent runs at by using the Control Panel Services app.

CEWII

shalabh.sachdeva SSC-Addicted Points: 422 More actions · Answer 5

Hi Elliott,

Please tell where should I look for the permissions for my SQL Server Agent account which is executing the windows job to RUN SSIS (further invoking a task to run batch file on a different server).

Thanks

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

You need to be sure what user the SQL agent is using, you can us the control panel Services application to figure that out. Then you need to check the share permissions on the remote server to see if that user has access and then to the file that you are trying to access.

I want to point out that when you run that script it will be running on the local server NOT the remote server.

Let me know how this is going.

CEWII

nivas0108 SSC Rookie Points: 47 More actions · Answer 7

Also, DTExec: The package execution returned DTSER_FAILURE (1). at the end of the logs.

/run /s \\chi-nt-damlais1 /tn "bat_file_test" is what is been passed as an argument inside the SSIS step to invoke the windows schedular at remote server.

One more thing, when I execute the SSIS from Visual basic alone, there is no problem atall. The problem is only when sql job calls this SSIS it ends up successful but without invoking the bat_file_test windows schedular at remote server.

Hope I make you understand what the problem is..

Can you help. Thanks.

srinivas