SQL Agent PowerShell job runs in ISE but not in SQL Servere

Question

My PowerShell script uses WinSCP to download files. It writes a message to a sql server table containing the number of files downloaded. When run within ISE it's fine. When run as a sql agent job it fails with error msg 'You cannot call a method on a null-valued expression.' This error is generated by the variable that the file count is assigned to. The variable is $fileCount.

The code is below. See variable $fileCount. I suspect that the beginning $ in the variable name is causing sql to misinterpret the variable in some way but I'm not sure. Any help is appreciated.

# Step 1 - Powershell script to download xml files to local folder.
param (
 $remotepath = "./",
 $mask = "*.xml",
 $destination = "D:\Files\Downloaded"
)
try
{
 # 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 = "hostname"
 UserName = "user"
 Password = 'password' 
 SshHostKeyFingerprint = "fingerprint"
 }
 $session = New-Object WinSCP.Session
 $fileCount = 0
 $elements = 0
 $errOut = $null
 try
 {
 $session.Open($sessionOptions)
 $directory = $session.EnumerateRemoteFiles($remotepath, $mask, [WinSCP.EnumerationOptions]::None)
 $elements = $directory.count
 **$fileCount = $elements.count**
 $transferOptions = New-Object WinSCP.TransferOptions
 $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
 $transferResult = $session.GetFiles($remotepath, $destination, $False, $transferOptions)
 $transferResult.Check()
 } # end try
 finally
 {
 $session.Dispose()
 }
} # end try
catch
{
 $errOut = ($_.Exception.Message)
}
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "ConnectionString;"
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand
$SqlCommand.CommandText = "EXEC Stored_Proc @chvMessage, @chvFileIndex, @chvRecordCount"
$SqlCommand.Connection = $SqlConnection
$SqlCommand.Parameters.Add("@chvMessage", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvMessage'].Direction = [system.data.ParameterDirection]::Input
$SqlCommand.Parameters.Add("@chvFileIndex", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvFileIndex'].Direction = [system.data.ParameterDirection]::Input
$SqlCommand.Parameters.Add("@chvRecordCount", [system.data.SqlDbType]::nvarchar) | Out-Null
$SqlCommand.Parameters['@chvRecordCount'].Direction = [system.data.ParameterDirection]::Input
$SqlConnection.Open()
if ($errOut -ne $null)
{
 $SqlCommand.Parameters['@chvMessage'].Value = $errOut
}
if ($errOut -eq $null)
{
 $SqlCommand.Parameters['@chvMessage'].Value = "**** " + $fileCount.toString() + " FILES DOWNLOADED"
}
$SqlCommand.Parameters['@chvFileIndex'].Value = ''
$SqlCommand.Parameters['@chvRecordCount'].Value = ""
$SqlCommand.ExecuteNonQuery() | Out-Null
$SqlConnection.Close()

• Jonathan Fite: The powershell engine that runs as a job step is not the same as the one that runs from the ISE. My advice is to save this as a .ps1 file on the server and then invoke it as a straight cmd job step. See my answer dba.stackexchange.com/questions/148317/… for details.
• Martin Prikryl: But you code seems strange anyway. You are effectively calling $directory.count.count - What do you expect this to do?
• PWC: I found that $directory.count returns a list, $directory.count.count returns the number of files.
• Martin Prikryl: OK, anyway, did you try to find out what statement throws the exception - and consequently, what object is null?
• PWC: This the statement that throws the exception because $fileCount is null. The mystery to me is why it is never set. This doesn't occur in ISE. $SqlCommand.Parameters['@chvMessage'].Value = "**** " + $fileCount.toString() + " FILES DOWNLOADED"