Copy files based on Query Results

Question

Post reply

Copy files based on Query Results

jeff451

SSChasing Mays

Points: 636
More actions
December 5, 2011 at 5:22 am

#253016

I am new to PowerShell and the online resources do not seem to have an example of my specific issue.
I have a multiple step SQL Agent Job and one of the steps is to copy a number of files from one folder to another folder on a remote computer. I can make this work if the source of the filenames is a text file and I use get-item and hard code the destination folder to a variable before starting the foreach loop.
I need to be able to select the source and destination from a view and pass those values as variables to the test-path, new-item and copy-item cmdlets below.
I found the code below online and have tried to adapt it to my requirements by modifying the Foreach command. The error is "cannot bind variable to -path because it is null". The SQL view returns full path names for source and destination in the format "\\servername\sharename\folder\folder\folder\filename.ext"
param([string]$sqlinstance="server\instance")
$result = Invoke-Sqlcmd -ServerInstance $sqlinstance -Query "SELECT top 10 Destination, Source FROM dbname.dbo.FilesToCopy ORDER BY DocID asc"
foreach ($Source in $result)
{
$src = $_.Source
$dest = $_.Destination
if(!(test-path -path $dest))
{
if((get-item $src).PSIsContainer)
{
new-Item $dest -type Directory -force | out-Null
Copy-Item “$src\*” $dest -recurse
“Folder $dest Missing. Creating it!”
}
else
{
new-Item $dest -type File -force | out-Null
Copy-Item $src $dest -force
“File $dest Missing. Creating it!”
}
}
else
{
if((get-item $src).PSIsContainer)
{
Copy-Item “$src\*” $dest -recurse
“Copy file done for $src”
}
else
{
Copy-Item $src $dest -force
“Copy file done for $src”
}
}
}
Jeff451SQL Guru wannabe

Viewing 3 posts - 1 through 2 (of 2 total)

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

bruce 1565 SSCrazy Points: 2574 More actions · Answer 1

If you write $result to the console you'll probably see the error right off. Try changing

foreach ($Source in $result)

{

$src = $_.Source

$dest = $_.Destination

...

To

foreach ($Source in $result)

{

$src = $Source.Source

$dest = $Source.Destination

...

jeff451 SSChasing Mays Points: 636 More actions · Answer 2

Thank you Grasshopper! That did work.

Jeff451SQL Guru wannabe