Powershell script on SQL agent no longer working

Advertisement

Kai_H
Joined:
Posts:
2
Location:
Canada

Powershell script on SQL agent no longer working

Hi,

I created a powershell script that is scheduled in SQL Agent(2012), it is one of many steps in a process to download and process data. Since Oct 2 this script is no longer executed - on this day Security Update for Windows (KB4522007)
- it appears that the library cannot be loaded when executed from SQL agent - I can execute the script as user logged in on the machine via RDP or a scheduled task in the Windows task scheduler. I suspect that this the access to dll files has been restricted to the SQL agent as a measure of increasing security. How else can I refer to the assembly? Can I move the file(s) to another location that SQL agent has access to?

Below the error message:

PowerShell script. The corresponding line is ' Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\WinSCP\WinSCPnet.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. '. Process Exit Code -1. The step failed.\

Thanks for any hint what I could do to fix this.

Kai

Reply with quote

Advertisement

Kai_H
Joined:
Posts:
2
Location:
Canada

Powershell script on SQL agent no longer working

I downloaded .Net framework update package and Powershell install but the error still occurs.
the current Power shell version is 5.1.14409.1005 - below the installed .Net frameworks.
PSChildName Version Release
----------- ------- -------
v2.0.50727 2.0.50727.4927
v3.0 3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation 3.0.6920.4902
v3.5 3.5.30729.4926
Client 4.6.01055 394271
Full 4.6.01055 394271
Client 4.0.0.0
The code below executes from powershell and can be called from Microsoft scheduler, but when executed in SQL server Agent I get the error message. This has been working for years - this error started on Oct 3rd - on this day the security update below was installed, not sure if that caused the error by restricting access to the library.
Here is the code that I have been using - I inserted the set Complus line and referred to the newer .net build that is installed - but this does not make a difference.

set COMPLUS_version=v4.6.01055.394271

# Load WinSCP .NET assembly
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"

# Setup session options
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
Protocol = [WinSCP.Protocol]::sftp
HostName = "deleted"
UserName = "deleted"
Password = "deleted"
SshHostKeyFingerprint = "deleted"[/b][/code]
}

$session = New-Object WinSCP.Session


# Connect
$session.Open($sessionOptions)

# set filenames - if executed on a monday subtract three days

if((get-date).dayofweek -match 'Monday')
{$filedate = (Get-Date).adddays(-3)}
else
{$filedate = (Get-Date).adddays(-1)}

$file1 = 'TORON_RH' + $filedate.ToString('yyyyMMdd') + '.csv'
$file2 = 'TORON_RC' + $filedate.ToString('yyyyMMdd') + '.csv'
$file3 = 'TORON_RT' + $filedate.ToString('yyyyMMdd') + '.csv'

# Download files
$transferOptions = New-Object WinSCP.TransferOptions
$transferOptions.TransferMode = [WinSCP.TransferMode]::Binary

$transferResult =
$session.GetFiles("/users/TORON/RPT/" + $file1, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
$transferResult.Check()

$transferResult =
$session.GetFiles("/users/TORON/RPT/" + $file2, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
$transferResult.Check()

$transferResult =
$session.GetFiles("/users/TORON/RPT/" + $file3, "\\ta_nas\Cidel\ReconData\NBCN\Toron\", $False, $transferOptions)
$transferResult.Check()


# Disconnect, clean up
$session.Dispose()

Reply with quote

Advertisement

You can post new topics in this forum