July 16, 2012 at 12:40 pm
Hello All -
I have a SSIS package which has 3 steps ( select the date, create a zip file and export the zip file to client server) it executes perfetly fine when I exeute from business intelligence studio . But, it fails as a scheduled job at the last step of execution.
Below is the error message :
Executed as user: DB\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:02:26 PM Error: 2012-07-16 14:17:18.18 Code: 0xC0029151 Source: Trasfer Gzip file to Destination Execute Process Task Description: In Executing "C:\xxxx.bat" "" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:02:26 PM Finished: 2:17:18 PM Elapsed: 892.077 seconds. The package execution failed. The step failed.
Can some one help me to fix this
July 16, 2012 at 1:55 pm
Well, not enuf in the error message for me to take a shot, so I'd recommend taking the CommandLine text from the job step and running from command prompt.
type dtexec
add a space then right-click to paste in CommandLine
This will hopefully give more info on what the problem is.
If I had to guess, I'd say Permissions, that the agent can't get to the file in question.
Tripz (7/16/2012)
Hello All -I have a SSIS package which has 3 steps ( select the date, create a zip file and export the zip file to client server) it executes perfetly fine when I exeute from business intelligence studio . But, it fails as a scheduled job at the last step of execution.
Below is the error message :
Executed as user: DB\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.5000.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:02:26 PM Error: 2012-07-16 14:17:18.18 Code: 0xC0029151 Source: Trasfer Gzip file to Destination Execute Process Task Description: In Executing "C:\xxxx.bat" "" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:02:26 PM Finished: 2:17:18 PM Elapsed: 892.077 seconds. The package execution failed. The step failed.
Can some one help me to fix this
July 16, 2012 at 2:33 pm
Hi,
I went to the command prompt and tried executing the command from batch file.
Please check the below command and msg that I am getting
C:\>dtexec pscp.exe -q -pw xxxxx -batch C:\aaaa.gz bbbb@yyyy:/import_files/aaaa.gz
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.5000.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Option "pscp.exe" is not valid.
I am using sql server 2005 and the
I forced the execution type of my SSIS package to 'Int32'
any suggestion please ?
July 16, 2012 at 2:37 pm
Tripz (7/16/2012)
Hi,I went to the command prompt and tried executing the command from batch file.
Please check the below command and msg that I am getting
C:\>dtexec pscp.exe -q -pw xxxxx -batch C:\aaaa.gz bbbb@yyyy:/import_files/aaaa.gz
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.5000.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Option "pscp.exe" is not valid.
I am using sql server 2005 and the
I forced the execution type of my SSIS package to 'Int32'
any suggestion please ?
I'm sorry, I have no familiarity with pscp.exe and its switches, all of my dtexec command lines start with /SQL
My gut feel is that agent cannot access that file.
July 18, 2012 at 11:43 am
mmm.. I am looking for the permissions, noting worked so far.
that exec is just putty console exe, and it works fine as a standalone SSIS from business intelligence
July 18, 2012 at 4:32 pm
I also have been fighting this fight. Originally experiencing what many others as well are seeing; package runs fine when 'right clicked', but fails when run from SQL Server Agent (scheduled job). I have made progress, in that my latest results are that my jobs are succeeding but the server agent is reporting failures. I offer a suggestion below based on my experiences and results in hopes that they will be helpful to you. I should preface this post with the acknowledgement that I am not a certified SQL professional, however, I have certainly done my due diligence by reading white papers, watching MS videos, calling SQL professional friends, and so forth, regarding this subject, so if I am missing a fundamental point, I apologize and would bet that I am not the only one.
Some of the paths that I followed that ultimately did not seem to have any affect on the result are these:
Experience: The often provided suggestion to set the 'Use 32 bit runtime' Execution option was not a fix.
Experience: The often provided 'package permissions' solution was not a fix for me.
Experience: The often provided suggestion of using a proxy did not resolve the issue for me.
My suggestion:
Bite the bullet and either use, or set up a DOMAIN account that the SQL Server Agent Service can be set to log on with. I say 'bite the bullet' because I can find no MS documentation that states explicitly that the agent service must startup and logon with a domain account in order for network facing processes to succeed (even if the process carries it's own domain level credentials).
The reality seems to be that if your job includes any interaction with another machines, you MUST have the SQL Server Agent Service start up with a domain level account. Apparently, no local account can successfully evaluate the permissions of an account specified within a job step. (Does anyone know this to be a fact; that the credentials given to an individual step can not be evaluated and processed/passed unless the agent starts up with a domain account?)... seems to me that if the server is registered with Active Directory and the AD helper service is active that this would not be neccesary. Maybe this is a bug in the way the agent service interacts with the other systems (since most people report the package runs fine with a right click-execute package and only fails via the job scheduler). The bottom line is that it seems that if your SQL Server Agent Service was started with any local account, it won't matter if your job step is set to run with Domain\GodOfTheDomain credentials, it won't matter, because the package will never pass validation when run via the scheduled agent job.
Oh, here is one good tip I got from a friend (on the phone - not the web... go figure!)
The error messages in the job history are rather unhelpful, shall we say, but you can get more detail in the log by going to the 'Job Step Properties' dialog window, clicking on 'Advanced' under the 'Select a page' section and then clicking/selecting 'Include step output in history' Result, you will get more (slightly) more useful information in your job history.
July 19, 2012 at 5:17 am
Also check that the exit code from the actual application / command. I have seen instances where an exit code other than 0 is returned, but that code still means a successful run of the command to that particular command. However, the SQL Agent sees anything other than 0 as a fail.
Robocopy is a good example of returning various return codes with a successful run. I know that this robocopy example may not be directly related, but maybe this link will help you address your issue: http://www.sqlsolutionsgroup.com/blog/index.php/2011/07/using-robocopy-in-sql-server-agent-jobs/[/url]
July 19, 2012 at 9:33 am
Tripz,
you may have thought of this already, but when you execute as a job on the server, the C: drive is the one on the _server_ C: drive, so make sure that your zip utility is where you expect it to be 😉
July 24, 2012 at 12:11 pm
Hi
Thank You for the helpful top
I did enable -- "" 'Include step output in history' ."" long before and trying to work for the fix
but did not find the solution yet !!!!
July 24, 2012 at 12:13 pm
The success exit code is 0 and is working fine when I execute the SSIS package separetly.
I am not seeing any issue when I execute standalone SSIS
but it fails as a job in the database
I am thinking to change the Sql Agent to run as a domain user and requested for it, I still need to get the approval and necessary permissions to do so.
July 24, 2012 at 12:56 pm
I am betting that the domain account for your SQL Agent will bring success to your jobs as it did mine, but if you are like me, you will probably be left wondering why that was necessary (as in required - no option). If anyone has answers regarding that, please don't hold back. Personally, I feel that putting the SQL agent at the mercy of a usable network connection is an unnecessary risk, at least in some implementations where the majority of jobs are processing local data with only one or two actions reaching out to the network (and shouldn't the 'run as' functionality take care of such instances?). Please be sure to post the outcome, I am curious. I remember when scheduling something to run was the last thing a developer did because it only took a few minutes and always worked. I will continue to seek a solid understanding on this subject and share any enlightenments I experience. Thanks to all for your input.
July 25, 2012 at 3:38 am
Create a credential which aliases to a domain account with the correct permissions and then a proxy to use that credential. In your sql agent jobstep to run the ssis select the proxy account at "run as "
That way the agent doesn't have the domain rights, just the job step.
I use this way for hundreds of jobs without a problem.
July 27, 2012 at 1:06 am
In addition I use specially created domain accounts for the various sql agents as they need to validate the proxy account. They don't have privileges outside the server.
July 27, 2012 at 1:11 pm
I would bet that the problem is as you have been advised and you are already aware that the SQL Server Service is not running as a Domain Account.
Can you have permission to change the Account?
If so set it to your domain account temporarily your Account it should work but you may not be able to do so depending upon your environment.
I would not expend any more energy on this until you are able to change the service account.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply