Problem executing REMOTE powershell script using WinSCP via SQL Server Agent Job

Question

On SQL Server 2016 I have setup a job that executes a powershell script that resides on a remote app server. When I execute the powershell script via the app server using the Powershell ISE app my script works without issue. When I had setup the job and enter this command:

powershell.exe -ExecutionPolicy Bypass -file "\\serverapp1\c$\coverageverifier_scripts\SFTP_CoverageVerifier.ps1" in Step 1.

When I look at the VIEW HISTORY I see the error below but I cannot figure out why the script now cannot load the file or assembly.

Any help/direction would be appreciated. Here is the error:

The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is 'powershell.exe -ExecutionPolicy Bypass -File "\empqaapp1\c$\coverageverifier_scripts\SFTP_CoverageVerifier.ps1"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Add-Type : Could not load file or assembly '

Here is my powershell script as well:

# Load WinSCP .NET assembly
#Add-Type -Path "WinSCPnet.dll" 
Add-Type -Path (Join-Path $PSScriptRoot "WinSCPnet.dll")

# Declare variables 
$date = Get-Date
$dateStr = $date.ToString("yyyyMMdd")

# Define $filePath
$filePath = "C:\coverageverifier_scripts\TEST_cvgver.20190121.0101"

# Write-Output $filePath

# Set up session options for VERISK TEST/ACCEPTANCE SFTP SERVER
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = "secureftpa.iso.com"
    UserName = "account"
    Password = "pw"
    SshHostKeyFingerprint = "ssh-rsa 1111 xxx/xxxxxxxxx+3wuWNIkMY5GGgRObJisCPM9c9l7yw="
}

$session = New-Object WinSCP.Session 
$session.ExecutablePath = "C:\WinSCP\WinSCP.exe"

try
{
    # Connect
    $session.Open($sessionOptions)

    # Transfer files
    $session.PutFiles($filePath,"/").Check()
}
finally
{
    $session.Dispose()

Seems you're missing a backslash in the path? => `"\\serverapp1\c$\coverageverifier_scripts\SFTP_CoverageVerifier.ps1"` — marsze, Jan 28 '19 at 14:14
I added the additional backslash in the path and now I am displaying the error I got above. I did confirm with our server admin our SQL server does have access to this share. Also, I included the PS script but again it works locally from the app server. Any suggestions? Thanks. — Melinda, Jan 28 '19 at 14:39
The error is pretty clear. `Add-Type` cannot find the path "WinSCPnet.dll". Most likely because your script is run from a different location. Make sure the DLL is there and maybe specify the full path. — marsze, Jan 28 '19 at 14:48
Also, make sure you unblocked that dll (right click on the file, and then see properties) on sql machine — Mike Twc, Jan 28 '19 at 16:12

score 0 · Answer 1 · answered Jan 28 '19 at 15:06

0

Your Add-Type call does not include the path to the WinSCPnet.dll.

If you have the WinSCPnet.dll in the same folder as your PowerShell script, use this syntax:

Add-Type -Path (Join-Path $PSScriptRoot "WinSCPnet.dll")

Or use a full path.

See also Loading assembly section in WinSCP .NET assembly article on Using WinSCP .NET Assembly from PowerShell.

answered Jan 28 '19 at 15:06

Martin Prikryl

188,800
56
490
992

Hi Martin, I do have the WinSCPnet.dll file in the same folder as my powershell script and I did modify my powershell script to include the Add-Type -Path (Join-Path $PSScriptRoot "WinSCnet.dll") and I got the error above. – Melinda Jan 28 '19 at 15:19
What "error above"? do you mean that you still get the same error? – Martin Prikryl Jan 28 '19 at 15:27
The error information returned by PowerShell is: 'Add-Type : Could not load file or assembly ' – Melinda Jan 28 '19 at 15:28
Did you try to *"execute the powershell script via the app server using the Powershell ISE app"*, while not having the the script's directory as the working directory? – Martin Prikryl Jan 28 '19 at 15:29
No, how would that modification look like in my powershell script? Sorry not knowing exactly where to make that mod. Thanks. – Melinda Jan 28 '19 at 15:35
Martin, I commented out the Add-Type lines of code on the app server and it ran just fine. – Melinda Jan 28 '19 at 15:44
However, when I executed the code from the SQL Server Agent Job then I got multiple errors. I'm just going to try and work through each error and see if I can make anything work. Thanks for your time. – Melinda Jan 28 '19 at 16:01
One more question, would INVOKE-COMMAND make any difference? – Melinda Jan 28 '19 at 16:02
I have no idea where/how you would like to use `Invoke-Command` + I do not think removing `Add-Type` is the solution. The error you are getting are probably result of you removing the `Add-Type`. – Martin Prikryl Jan 28 '19 at 16:06
You told me to execute the powershell script while NOT having the script's directory as the working directory on the app server. I did that by commenting out the APP_TYPE line in my powershell script. I asked about the INVOKE-COMMAND because while googling it seemed to be another option to execute powershell scripts on REMOTE servers. – Melinda Jan 28 '19 at 16:11
I assume you mean `Add-Type`, not `APP_TYPE`. I did not want you to remove `Add-Type`. Do you even know what working directory is? – Martin Prikryl Jan 28 '19 at 16:19
Where my script it located is the working directory. – Melinda Jan 28 '19 at 16:31
No it's not. And that's mostly likely the problem. See https://en.wikipedia.org/wiki/Working_directory – Martin Prikryl Jan 28 '19 at 16:33
I am currently logged into the app server and working in the directory where my script is located, \\empqaapp1\c$\coverageverifier_scripts\SFTP_CoverageVerifier.ps1 I am also working on the SQL server which is a separate box that is executing the ps1 file in the app server. Thanks – Melinda Jan 28 '19 at 17:05

Problem executing REMOTE powershell script using WinSCP via SQL Server Agent Job

1 Answers1