March 2, 2011 at 9:56 pm
This is not a request for help... it may help if anyone else has problems running SSIS packages that contain Execute Process Tasks as automatic SQL Server Agent jobs.
I have a package that has to call a utility to calculate checksums for a number of extract files. I was running the utility with the Execute Process task, passing in the command line and the arguments via variables. The package worked fine in BIDS, fine in Integration Services, even when running as an Interactive Agent job. The problem only started when I tried to schedule the job... it would get to the Execute Process Task and just hang.
I tried running the job under a various proxies, but nothing seemed to work. Finally, in desperation, I changed the Execute Process task to an Execute SQL Task that executes a stored procedure, that runs the utility via a call to the xp_cmdshell.
The stored procedure looks similar to the following:
create procedure dbo.usp_exec_checksum_utility
(
@command varchar(1000)
)
as
declare @command_line varchar(1000)
set @command_line = '''' + @command + ''''
exec master..xp_cmdshell @command_line
You can't pass the @command variable directly to the xp_cmdshell call - you have to wrap the variable value in the single quotes. Of course, you could set the value of @command_line inline, but the problem with that is, you have to recompile the stored procedure every time you change the command line (as in every time you want to deploy to a new server). Having an @command variable means you can control the value of @command via SSIS configurations. Which is Really, Really Good.
I hope this helps other people who come across something similar. 😀
March 3, 2011 at 5:01 am
Do you have an idea why the original problem occurred?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply