2

I have a simple PowerShell script that runs fine from the console (PSVersion 2.0).

$psftpPath = "C:\Program Files (x86)\PuTTY\psftp.exe"
$sftpHost = "foo.baa.com"
$userName = "myusername"
$userPassword = "mypassword"
$todate =   Get-Date -format yyMMdd;
$fromdate   = (get-date).AddDays(-7).ToString("yyyMMdd")
$filename =  "RawData_Extract_$($fromdate)_$($todate).zip"
$cmd = @("lcd D:\Healthstream", "get $($filename)", "bye")
$cmd | & $psftpPath -pw $userPassword "$userName@$sftpHost"

I want to schedule it as a SQL Server Scheduled Job. When I set the "Type:" of job to PowerShell it fails with a Syntax Error on (or around) the $cmd line (line 9).

Can anyone help me understand what I'm doing wrong?

Colin
  • 121
  • 2

1 Answers1

0

UPDATE:

It looks like this can be resolved using this stackoverflow solution -> https://stackoverflow.com/questions/12923074/how-to-load-assemblies-in-powershell

It looks like you'll need to instantiate the psftp.exe application as an assembly. MS has documentation on that here -> http://technet.microsoft.com/en-us/library/dd347574.aspx

Community
  • 1
user243406
  • 1
  • 1
  • Welcome to Server Fault! Generally we like answers on the site to be able to stand on their own - Links are great, but if that link ever breaks the answer should have enough information to still be helpful. Please consider editing your answer to include more detail. See the [FAQ](http://www.serverfault.com/faq) for more info. – slm Aug 06 '13 at 02:15
  • Forgive my ignorance but why do I need to load an assembly to run PowerShell in a SQL Server Agent Job? The syntax parses correctly and runs fine from within a PowerShell script running in the PowerShell console? – Colin Aug 06 '13 at 14:40
  • 1
    The work around is to call out to execute the script from within the SQL Server Agent Job, with "PowerShell D:\MyScript.ps1". Not ideal as this is an exception to our standard but it works. I still want to know why the content of this script won't run from within the Agent Job Step itself... – Colin Aug 06 '13 at 18:08