Cannot use Powershell to UNZIP files inside SQL Job

Question

Post reply

Cannot use Powershell to UNZIP files inside SQL Job

Fred Woolverton-442598

Right there with Babe

Points: 715
More actions
May 26, 2015 at 10:36 am

#398503

Server is running Windows 2008R2 Standard. SQL Server is 2008R2 SP2
Code:
$path = "I:\SQLBackupsFromPROD"
$destination = "I:\SQLBackupsFromPROD\CaaS_Backups"
$shell_app= New-Object -com shell.application
$files = Get-ChildItem -Path $path -filter *.zip -recurse
foreach($file in $files) {
$zip_file = $shell_app.namespace($file.FullName)
$copyHere = $shell_app.namespace($destination)
$copyHere.Copyhere($zip_file.items())
}
It is set to run as the SQL Server Agent Account. I have logged into the server using that account and I can run the script successfully.
When I put it into a SQL Job Step and run it, it shows SUCCESS but no files are unzipped.
"Message
Executed as user: <Username>. The step did not generate any output. Process Exit Code 0. The step succeeded."
:crazy:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Have you tried writing anything to a log file? For example, a list of files selected?

Does the script work outside of SQL Agent?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Fred Woolverton-442598 Right there with Babe Points: 715 More actions · Answer 2

Have not tried the Log File trick. Did try capturing the Job Step output to a TXT file but it was blank.

I did log onto the box using the SQL Agent credentials and was able to run this script successfully in PS and SQLPS. Since the SQL Job is supposed to cal SQLPS.exe to run the script and the script does run when I call SQLPS manually I am at a complete loss as to why it will not run inside a job step.

Fred

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

You could try logging $env:username from the script file to be sure.

Also, are your paths absolute? What is the current directory? Find out using:(Resolve-Path .\).Path

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Fred Woolverton-442598 Right there with Babe Points: 715 More actions · Answer 4

Path checked out OK.

Revised the script somewhat:

$path = "\\Servername\SQLBackupsFromPROD"

$destination = "\\Servername\SQLBackupsFromPROD\CaaS_Backups"

$Extension = "*.zip"

$shell_app= New-Object -com shell.application

$Files = Get-Childitem $path -Include $Extension

#Format-List -InputObject $Files

foreach($file in $files) {

$zip_file = $shell_app.namespace($file.FullName)

$copyHere = $shell_app.namespace($destination)

$copyHere.Copyhere($zip_file.items())

}

Now I get this error in SQLPS:

Get-ChildItem : Invalid Path: '\\ATXSQL50DV'.

At line:1 char:23

+ $Files = Get-Childitem <<<< $path -Include $Extension

+ CategoryInfo : NotSpecified: (:) [Get-ChildItem], ArgumentException

+ FullyQualifiedErrorId : System.ArgumentException,Microsoft.PowerShell.Commands.GetChildItemCommand

But the script runs fine in powershell.

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

I believe that SQLPS is defaulting to a different provider than the PowerShell command line so if you change the following line:

$path = "\\Servername\SQLBackupsFromPROD"

to this:

$path = "FileSystem::\\Servername\SQLBackupsFromPROD"

then you are explicitly specifying Get-Item to retrieve from the file system.

Let me know how you get on.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Fred Woolverton-442598 Right there with Babe Points: 715 More actions · Answer 6

Your revised script worked GREAT in SQLPS. Pasted it in. Ran it. Unzipped the files to the correct folder.

HOWEVER,when I used the same script as a Job Step, it made like the Energizer Bunny and kept running and running and running. Even tried it as a CmdExec calling the ps1 file. Same thing.

Thank you for your help.

Gary Varga SSC Guru Points: 82166 More actions · Answer 7

Not sure why that would happen. I guess you need some diagnostic logging in there.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

David Schwantner-383412 SSC Enthusiast Points: 183 More actions · Answer 8

We had the same exact problem with something we were working on yesterday (same use case, same copyhere and movehere, same successful completion but nothing actually happening....).

After searching for an answer (and finding this post as the closest thing, but no answer), and trying a lot of things we solved it by:

1) In the agent job, create an "Operating system (CmdExec)" step.

2) In the "Command" put "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe c:\script_location\script_to_call_the_other_script.ps1". (or whatever the path is to powershell and the place where you store your scripts)

3) This calls a new script that has one line in it with the full path and name of the real script you want to run and any parameters you need to pass.

I don't know why it needs to wrapped in so many layers, but it works now. Tested and in production and happy customers!!!

DS