June 10, 2010 at 3:09 am
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~
June 10, 2010 at 3:11 am
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
June 10, 2010 at 12:01 pm
I completely agree, it is VERY often permissions. And absolutely the first place to start..
CEWII
June 11, 2010 at 1:19 am
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~
June 11, 2010 at 1:22 pm
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
July 7, 2010 at 6:02 am
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
July 7, 2010 at 10:01 am
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
September 17, 2010 at 12:46 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply