0

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()
6
  • Seems you're missing a backslash in the path? => "\\serverapp1\c$\coverageverifier_scripts\SFTP_CoverageVerifier.ps1" Commented Jan 28, 2019 at 14:14
  • Thanks, marsze. I'll try that. Hopefully that is the issue. Commented Jan 28, 2019 at 14:18
  • Or the db server simply doesn't have access to that share. Commented Jan 28, 2019 at 14:21
  • 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. Commented Jan 28, 2019 at 14:39
  • 1
    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. Commented Jan 28, 2019 at 14:48

1 Answer 1

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.

Sign up to request clarification or add additional context in comments.

14 Comments

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.
What "error above"? do you mean that you still get the same error?
The error information returned by PowerShell is: 'Add-Type : Could not load file or assembly '
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?
No, how would that modification look like in my powershell script? Sorry not knowing exactly where to make that mod. Thanks.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.