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?

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

  • 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 for more info. – slm Aug 6 '13 at 2: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 6 '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 6 '13 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.