SSMS agent job using scp command to transfer files to sftp is going to loop and not completing

Have tried different approaches to make the SSMs job working with SCP command with perl script. but the job is going into loop with out having a result. PS : The script is working fine with running from command prompt directly.

command used in the perl script:-

$Command = "scp -i D:\File1\RS2\DataFeed\Code\PrivateKey.ppk -s $InternalFile admin@sftp.world.com:$VendorName/$DestFileName";

system command used in perl

system($command);

While running the command directly from windows cmd it is correctly placing file to the SFTP. but while running this perl script from ssms agent job it seems not working and the job is keep running without any results. Any possible leads to the actual errors will be much appreciated

Detailed Steps :

Job in SSMS :

Step :

DataFeed.cmd

%_Debug% echo off


cd /d %0\.. 
pushd .
setlocal

rem -----------------------------------------------------------------
rem Localize environment
rem -----------------------------------------------------------------

if exist DataFeed_Environment.cmd (
    call DataFeed_Environment.cmd
) else (
    echo DataFeed_Environment.cmd not found!!!
    echo
    goto CmdUsage
)



rem -----------------------------------------------------------------
rem Run perl package
rem -----------------------------------------------------------------

C:\Perl\bin\perl.exe DataFeedProd1.pl
if %ERRORLEVEL% NEQ 0 goto ErrorExit

goto Exit

rem -----------------------------------------------------
rem Command Usage
rem -----------------------------------------------------
:CmdUsage
    Echo ---------------------------------------------------------------------
    echo.
    echo DataFeed.cmd
    echo  Wraps the call to DataFeed.pl,
    echo  mails log upon errors.
    echo.
    echo Usage:
    echo DataFeed.cmd
    echo.
    echo ----------------------------------
rem endlocal
rem popd
rem exit 1


rem -----------------------------------------------------------------
rem Error exit
rem -----------------------------------------------------------------
:ErrorExit
echo DataFeedProd1.pl failed !!!
echo 
rem endlocal
rem popd
rem exit  1


rem -----------------------------------------------------------------
rem Exit
rem -----------------------------------------------------------------
rem endlocal
rem popd
:Exit

rem exit  0

sub CopyDataFeedFileToSftp{

   my ($DataFeedFileInternal, $DataFeedVendorName,$DataFeedFileName) = @_;
   my($DestFileName)=$DataFeedFileName.".zip";
   my($Command);
   my($RetValue) = 1;

   $Command = "C:\\Users\\hprasu\\Downloads\\OpenSSH-Win64\\scp.exe -i D:\\File1\\RS2\\DataFeed\\Code\\PrivateKey.ppk -s $DataFeedFileInternal a_Tne\@nasftp\.egencia.com:$DataFeedVendorName/$DestFileName";
   $RetVal = &CallSystem($Command);
   if ($RetVal == 0) {
      &AppendFileToLog($TempFile);
      &ErrorExit("Unable to copy data feed file using SCP command:\n".$Command);
   }
}

The above perl method is executing the System command

Solution

You should:

have full path to perl.exe and your perl script in job's command
escape all special characters in interpolated strings for Perl and
use full path for scp command since operating system don't know where scp.exe is located (until it in the $PATH):
check filesystem permissions for all files in the command and perl script. Job should has access those files.

So command would be

$Command = "full_path\\scp.exe -i D:\\File1\\RS2\\DataFeed\\Code\\PrivateKey.ppk -s $InternalFile admin\@sftp.world.com:$VendorName/$DestFileName";

Read this: https://www.geeksforgeeks.org/perl-quoted-interpolated-and-escaped-strings/