Why does the .NET Folder.CopyHere Method not allow dialog suppression for .ZIP files when run within a SQL Server Agent PowerShell job step?

I am using PowerShell in a SQL Server Agent job step to automate the extraction of .ZIP file contents to a directory.

Based upon highest-voted answer on this question: How to zip/unzip files in Powershell?

I am using this code:

$dir_source = "\\myserver\myshare\"
$dir_destination = "\\myserver2\myshare2\"
$file_source = Get-ChildItem -Path (Join-Path -Path $dir_source -ChildPath "test.zip")

$shell_app = New-Object -com shell.application
$zip_file = $shell_app.namespace($file_source.FullName)
$destination = $shell_app.namespace($dir_destination)
$destination.Copyhere($zip_file.items(),20)

The vOptions optional parameter of the CopyHere method is 20, which specifies "Do not display a progress dialog box." (4) and "Respond with "Yes to All" for any dialog box that is displayed." (16).

This parameter works as expected in a PowerShell script editor (I'm using PowerGUI Script Editor). I can run the script, and run it again (overwrite scenario), and the script completes with no errors and no dialog boxes. However, execution of the same code in a SQL Server Agent PowerShell job step results in a job that hangs when the file already exists in the destination database.

Steps to reproduce:

- Instantiate code in a SQL Server 2008 SQL Server Agent Job step
- Start the SQL job
- Result: SQL job completes, unzipped file "test.csv" appears in the $dir_destination folder
- Start the SQL job
- Result: Job executes indefinitely.
- Stop the SQL job
- Delete the "test.csv" from the $dir_destination folder
- Start the SQL job
- Result: SQL job completes, unzipped file "test.csv" appears in the $dir_destination folder

Why is it that the vOption parameter does not work for the SQL Job?

Walter Wang, of Microsoft Online Community Support says:

...please note each shell folder is backed by a shell namespace extension (NSE for short). Each NSE will choose to have its own mechanism to copy/move/delete data items (file/folder for a normal file system path).

The documentation you referenced about CopyHere method only applies to normal file system path. That's why you using option 4 to disable the progress dialog doesn't work for a zip folder.

On the other hand, last time I checked with shell team, currently the zip file NSE's functionality is only meant to be used with user interaction.

In other words, programmatically access to the zip file NSE is not officially supported."

I wonder if this has something to do with the internals of Powershell job steps in SQL Agent. For example you cannot do write-host in powershell job step in SQL Agent as explained in this blog post: http://blogs.msdn.com/b/mwories/archive/2009/09/30/the-use-of-write-host-and-sql-server-agent-powershell-job-steps.aspx

One suggestion-- try setting up a CmdExec job step and calling C:\windows\System32\WindowsPowerShell\v1.0\powershell.exe with the -file parameter instead of using powershell job steps. This way it will use cmdexec instead of sqlps.

You need to pass in the flag

(16)

Respond with "Yes to All" for any dialog box that is displayed.

Like so:

$destination.Copyhere($zip_file.items(), 16) 

You may want to combine that with this flag:

(4)

Do not display a progress dialog box.

So you would do:

$destination.Copyhere($zip_file.items(), 20)