Freigeben über

Integration services packages using Execute process task with WinZip32.exe fails intermittently, when run under a SQL Server Agent Job:

  • Artikel

This blog is about one of the known issues that anyone would run into when executing an Integration services package that contains an Execute Process Task calling a custom or 3rd party executable scheduled through a SQL Agent job. The scheduled job fails consistently or intermittently with errors pointing us to this Execute Process task.

In this blog, we will speak about one such 3rd party executable that is been used by a lot of us for the file compression functionality, WINZIP. When we try to execute a SSIS package that contains an Execute Process Task using the Winzip32.exe through Business Intelligence Development Studio [BIDS] or SQL Server Data Tools, the package executes fine without any issues. But when the same package is scheduled to run under a SQL Server Agent Job, it fails consistently/intermittently with the following error message,

Executed as user: <SQL Agent Account/Proxy>. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: HH:MM:SS PM Error: YYYY-MM-DD HH:MM:SS.MS Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Program Files (x86)\WinZip\WINZIP32.EXE" "-e -o z:\FolderName \Incoming\OutputFileName.zip z:\FolderName\Incoming\UnzipWorkingFolder" at "", The process exit code was "-1073741819" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: HH:MM:SS AM/PM Finished: HH:MM:SS AM/PM Elapsed: XXXX.XX seconds. The package execution failed. The step failed.

 

 

As we can see the process exit code for this application is not the expected "0" rather a "-1073741819".

This simply means that the process had terminated in an unexpected manner. When we look into this and perform an in-depth research, we will come across the following interesting points,

1. SQL Server Agent Job is an unattended scheduled service, that shouldn’t wait for any manual user inputs through GUI and basically works only with those command line applications that don’t wait for the user inputs.

2. You cannot call the winzip32.exe in a command line style, as in from a SQL Server Agent Job through a SSIS package. Rather this is not a recommended way as winzip32.exe is neither a command line tool nor can it be used in that fashion.

So how do we go about using winzip32.exe inside our package to achieve compression / uncompressing functionalities and schedule these packages using SQL Server Agent job and execute it successfully.

 

WinZip provides a command line add-on named WZZIP.exe and WZUNZIP.exe for this purpose. These files are located in the default installation folder of WinZip [By default: C:\Program Files (x86)\WinZip\]

The command line parameters for these command line executable are available online.

So we just replaced the "WINZIP32.EXE" with "WZUNZIP.EXE" or "WZZIP.exe" in command line as per the requirement and that did the trick.

Old command:

"C:\Program Files (x86)\WinZip\WINZIP32.EXE" "-e -o z:\FolderName \Incoming\OutputFileName.zip z:\FolderName\Incoming\UnzipWorkingFolder"

New command:

"C:\Program Files (x86)\WinZip\WZUNZIP.EXE" "-e -o z:\FolderName \Incoming\OutputFileName.zip z:\FolderName\Incoming\UnzipWorkingFolder"

Once we made the above changes and scheduled these packages to run under a SQL server Agent job it worked like a charm.

Author : Krishnakumar(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft

Comments

  • Anonymous
    May 06, 2014
    Hi I have same problem in my ssis package it run very well in SSIS service but when I configure it on SQL Agent, the files are create but it does not zip by 7Zip. I don't know what is issue in that, will you please help me to resolve this thing. Thanks in advance Mrugesh

  • Anonymous
    June 19, 2014
    This command is working but it does not extracting files I was already given -e command please help urgent

  • Anonymous
    May 12, 2015
    Excellent...