SSIS Package fails with Execute Process Task when run from SQL Agent Job

I have an SSIS 2008 package running on Win 2008 64-bit server. The package has an Execute Process Task that runs a net use command using cmd.exe to make a connection.

This runs successfully when tested from the command prompt and runs successfully when the package is run in Visual Studio 2008. However, when the package is called from a SQLAgent job the package fails with the error (actual servernames and passwords removed):

Failing Task Name = EPT Connect mapped drive Error Code = -1073573551 Error Detail = In Executing “C:WINNTsystem32cmd.exe” “/C net use

\servernamefoldername /USER:userid password” at “”, The process exit code was “1” while the expected was “0”.

I have determined that a successfull connection should provide a exit code of 0.

The job runs under a proxy account but as explained when the package has run from within VS 2008 it was also tested to run under the proxy account and is successful.

It is not a 64-bit issue as I have tested it by running the package using the 32-bit version of dtexec.exe and the same problem occurs.

Also I have found that by running a simple command process within the Execute Process Task such as dir *.* it is successful when run from the job indicating that it is in fact something related to the net use command itself when run from inside a job.

I have run out of ideas to pursue this – would appreciate anyone’s help ?

 

Let’s back up a second, OK?I need to ask this: WHY are you needing to have a mapped drive? Best Practices for connecting to resources in SSIS packages says that you should use the full path of

Hi Paul R W,Based on my testing, while running the package  under a sql  Server agent  proxy account, the password in the arguments can’t be verified.The workaround to fix the issue is that:

In response to Todd:The package  is using the full path of \server nameshare name and not actually uisng mapped drive letters. Apologies for the confusion but that is what I meant by having to map a drive. In response to Jen:I’m glad that you have been able to reproduce the issue but I’m not sure the workaround would be excepted by the DBA’s as this goes against the principle of least privilege. All the packages run  under a proxy account rather than the SQL Server Agent service