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"

    # 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

        $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)
     } # end try


} # end try

        $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

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
  • 2
    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. – Jonathan Fite Jun 17 at 15:51
  • But you code seems strange anyway. You are effectively calling $directory.count.count - What do you expect this to do? – Martin Prikryl Jun 18 at 6:31
  • I found that $directory.count returns a list, $directory.count.count returns the number of files. – PWC Jun 18 at 14:52
  • OK, anyway, did you try to find out what statement throws the exception - and consequently, what object is null? – Martin Prikryl Jun 18 at 15:20
  • 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" – PWC Jun 18 at 15:29

Your Answer

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

Browse other questions tagged or ask your own question.