September 2, 2009 at 8:56 am
I have an SSIS package to write three text files to disk, zip them up and email them. It works perfectly when logged in as myself, the SQL Server service account or the SQL Server Agent service account so I don't think it's a permissions issue but when run as a job it just hangs. The job successfully writes the text files so my suspicion is focussed on the zipping, for which I'm using an Execute Process Task with a command line zip program:
RequireFullFilePath: True
Executable: \\Dcf\data$\Shared\Reference\Royal London\PACOMP.EXE
Arguments: -a -c2 -spassword rl_export.zip export_a.txt export_b.txt export_c.txt
WorkingDirectory: \\Dcf\data$\Shared\Reference\Royal London
Can anyone give me any pointers?
Thanks
--
Scott
September 2, 2009 at 9:00 am
Have a look at the job history there should be an error logged in there..
September 2, 2009 at 9:11 am
The job step never completes so there's nothing in the history (apart from the stop job request I have to issue). The package runs in a couple of minutes but the job can be hanging for nearly 24 hours with no result.
--
Scott
September 2, 2009 at 9:22 am
Scott (9/2/2009)
The job step never completes so there's nothing in the history (apart from the stop job request I have to issue). The package runs in a couple of minutes but the job can be hanging for nearly 24 hours with no result.
Not sure if this will help, create seperate packages each for all the tasks that you are doing and execute that package as a job. Just to go to the next level of debugging to identify which task is failing? Not sure if you said its confirmed Zipping which is failing!
---------------------------------------------------------------------------------
September 2, 2009 at 9:25 am
I'll try that, but I'm sure it's the zip that's failing. The text files are written to disk but the zip file isn't.
--
Scott
September 2, 2009 at 9:32 am
Is there a logging option in that zipping tool?
---------------------------------------------------------------------------------
September 2, 2009 at 9:43 am
No, there's no logging option. I'm open to using other zipping tools, especially if they're free!
--
Scott
September 2, 2009 at 12:31 pm
sorry I am not sure exactly what could be the problem and could not reproduce it, but I do have similar setup where I do the zipping, I am using 'gzip'. In my case, I call a stored procedure within which I am calling XP cmdshell to execute the command and its working fine. Do you think its worth trying this, ( not the new tool but the same tool, executing it from an SP?)
May be add different steps in the job, one the package which creates files, second calling an sp which inturn will do the zipping and then calling another package to send the mail?
I think its too much to ask for, but you can try it as a last resort if nothing works? 🙂
---------------------------------------------------------------------------------
September 2, 2009 at 12:47 pm
Anything that you could see in eventvwr?
---------------------------------------------------------------------------------
September 3, 2009 at 3:58 am
I've gone down the xp_cmdshell route and it's working. It's a shame as it feels like a much less elegant solution but it's doing the job so I won't complain. Thanks for the suggestion - I'd got a bit too focussed on getting the Execute Process Task to work and hadn't considered approaching the problem in a different way, which is my useful lesson for the day.
--
Scott
September 3, 2009 at 4:09 am
Yes, I know its bit less elegant. Execute task should have worked! I dont know whats happening. Just to reproduce the problem, I tired to create execute task with my zip tool (gzip) and its just terminating abruptly (coult be the problem with my lappy OS which is vista, cant rule it out!)
---------------------------------------------------------------------------------
September 4, 2009 at 8:13 am
Scott (9/3/2009)
I've gone down the xp_cmdshell route and it's working. It's a shame as it feels like a much less elegant solution but it's doing the job so I won't complain. Thanks for the suggestion - I'd got a bit too focussed on getting the Execute Process Task to work and hadn't considered approaching the problem in a different way, which is my useful lesson for the day.
Have you tried using the GZIP component native to .NET libraries? You can get to it via the script component, which I use often and without issue. I refrain from the xp_cmdshell route because for all intents and purpose you loose the ability to manage the spid.
Josef Richberg
2009 Exceptional DBA
http://www.josef-richberg.squarespace.com
http://twitter.com/sqlrunner
September 4, 2009 at 8:21 am
Have you tried using the GZIP component native to .NET libraries? You can get to it via the script component, which I use often and without issue. I refrain from the xp_cmdshell route because for all intents and purpose you loose the ability to manage the spid.
The idea never crossed my mind. I must admit that now I have a working solution I'm not really inclined to tinker with it, but I'll bear your tip in mind for the next time I have to do something like this.
--
Scott
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply