June 21, 2013 at 4:44 am
I have a long running SSIS package that I have run manually so far via "Execute Package Utility". Now I created a job to schedule and run it. If I start the job, instead of the package, it completes in 1 second as successful. I thought maybe I commented out the command, but it looks fine:
exec xp_cmdshell 'dtexec /DTS "\MSDB\Website_FTP_Import" /SERVER MyServer /CHECKPOINTING OFF /REPORTING V '
I run other SSIS packages this way ..... strange. Maybe it's a permission thing somehow, but no errors.
Any ideas ?
June 21, 2013 at 5:44 am
Is there error-handling inside the package trapping a potential error?
'Only he who wanders finds new paths'
June 21, 2013 at 8:15 am
No, I haven't delved into that .... not a lot of SSIS experience. The package itself runs fine though when run "stand-alone". It doesn't run when invoked from a scheduled job, although it "succeeds" in 2 seconds.
June 21, 2013 at 8:48 am
There is a setting, which I cannot remember, where you have to specify the job to fail when the package does. Apologies I just cant think off the top of my head where that is. It could be that there is error handling in there too.
Remember when ran as a job it uses the SQL agent service credentials, when stand alone it will be using the logged in ones, that can throw up errors.
'Only he who wanders finds new paths'
June 29, 2013 at 5:38 pm
How do you know it "succeeded" if you're not capturing the return code from xp_cmdshell? xp_cmdshell won't throw an error just because dtexec returned a 1. Try capturing the return code in a variable and see if it is non-zero. If you're seeing output from the call to xp_cmdshell, what does it say? By default dtexec will write info, warning and error messages to stdout which xp_cmdshell should capture and return as a resultset.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2013 at 10:01 am
Resolved:
Seems I needed to set Protection Level = "Rely on server storage and roles for access control"
when importing the package from file system into MSDB.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply