4

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

Community
  • 1
peterk411
  • 178
  • 1
  • 8

2 Answers2

5

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)
smoak
  • 646
  • 2
  • 7
  • 13
  • This syntax is exactly right, and works for me when I run the script in PowerGUI Script Editor. However, the same script hangs when run as a SQL Server Agent PowerShell-type job step in SQL Server 2008. – peterk411 Aug 30 '11 at 22:19
  • Are you using the full path to the zip file and destination file? In your script you are using (Get-Location).Path which could cause issues. – smoak Aug 30 '11 at 23:30
  • I altered the script to explicitly specify the paths. Unfortunately, the result was the same. I've updated the question with the new version of the code and incorporated the results of your comments. I upvote you, sir! – peterk411 Aug 31 '11 at 21:34
2

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.

Chad Miller
  • 1,091
  • 8
  • 11
  • Thanks for weighing in, Chad, your question [Difference between PowerShell / SQL Server snap in's / tools](http://stackoverflow.com/questions/9500556/difference-between-powershell-sql-server-snap-ins-tools) got me thinking about this question again. It seems one cannot easily predict what methods will work reliably in a SQL2008 Agent job step. Until we upgrade to SQL 2012 (when the powershell host will be more standard), your suggestion is an acceptable work-around. – peterk411 May 08 '12 at 04:58