2

I've some .CSV files generated in a shared folder \\SERVER01\DIR\Current and would like to copy to another folder in same server \\SERVER01\DIR\Archive.

The script needs to copy, rename files adding timestamp, and then remove from the source folder. PS script works very well on PowerShell ISE or Windows PowerShell, but, when I try to schedule same script in a SQL Server Job, I'm receiving errors below:

$source = "\\SERVER01\DIR\Current" $destination = "\\SERVER01\DIR\Archive"

Get-ChildItem $source -Recurse -Include *.csv | % { $name = $.Name.Split(".")[0] + "" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" #$name = "Finished" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" #$name = "Finished" + $.Name.Split(".")[0] + "" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" Rename-Item $ -NewName $name Move-Item "$($_.Directory)$name" -Destination $destination }

I've received error below:

Message
Unable to start execution of step 1 (reason: line(9): Syntax error).  The step failed.

After receiving the error above, I've changed Line(9) as below:

$source = "\\SERVER01\DIR\Current"
$destination = "\\SERVER01\DIR\Archive"

Get-ChildItem $source -Recurse -Include *.csv | % { $name = $.Name.Split(".")[0] + "" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" #$name = "Finished" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" #$name = "Finished" + $.Name.Split(".")[0] + "" + ($.CreationTime | Get-Date -Format yyyymmdd) + "" + ($.CreationTime | Get-Date -Format hhmmss) + ".csv" Rename-Item $ -NewName $name Move-Item "\SERVER01\DIR\Current$name" -Destination $destination }

Then, received another error:

Message
The job script encountered the following errors. These errors did not stop the script:  
A job step received an error at line 4 in a PowerShell script. 
The corresponding line is 'Get-ChildItem $source -Recurse -Include *.csv | % {  '. 
Correct the script and reschedule the job. 
The error information returned by PowerShell is: 
'Cannot find path '\\SERVER01\DIR\Current' because it does not exist.  '.  
Process Exit Code 0.  The step succeeded.

After that, I've tried to change line(9) again, using $source parameter:

Move-Item $source + "\" + $name -Destination $destination

But, it didn't work and return same error above: 'Cannot find path '\SERVER01\DIR\Current' because it does not exist.'.

It looks like $($_.Directory)... is not working in a SQL Server job. Not sure why and how to solve it?

I'm using SQL Server Agent to schedule it to be executed after some others steps which are being executed on SQL Server in the same job.

Paul White
  • 94,921
  • 30
  • 437
  • 687
AdemirP
  • 21
  • 2

0 Answers0