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?
...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."