I have requirement to push the file to mainframe using sftp (tectia tool). Need to use .bat file and SQL Server Agent to schedule the .bat file.
SQL Server is installed on server A and tectia tool, source file (need to push) located on server B.
On Server A - I have created SSIS package and used Execute Process Task with following command
Executable C:\Windows\System32\cmd.exe
Arguments /c "\server B name\bat file folder\master_sftp.bat"
then deployed the package on server A. Created SQL Agent job that call deployed package.
On server B (where tectia and push file is located) I have added Userkeys (pub keys) into profile of Service account ID which is used to run SQL Server Agent.
master_sftp.bat has following line
sftpg3 --user=id@xyz.ab.org -B "\server B name\bat file folder\child_sftp.bat"
and in child_sftp.bat has following lines
bin
put "\server B name\file folder\abc.xlsx"
rename abc.xlsx abc_todaydate.xlsx
quit
when I run SQL Agent job I am getting below error sometimes
Executed as user: Service Account ID. C:\Windows\system32>sftpg3 --user=id@xyz.ab.org -B "\server B name\bat file folder\child_sftp.bat" 'sftpg3' is not recognized as an internal or external command, operable program or batch file. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:09:46 PM Error: 2018-03-30 17:09:46.59 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Windows\System32\cmd.exe" id@xyz.ab.org -B "\server B name\bat file folder\child_sftp.bat" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:09:46 PM Finished: 5:09:46 PM Elapsed: 0.219 seconds. The package execution failed. The step failed.
and sometimes getting error as
Executed as user: Service Account ID. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 5:11:48 PM Error: 2018-03-30 17:11:49.15 Code: 0xC0029151 Source: Execute Process Task Execute Process Task Description: In Executing "C:\Windows\System32\cmd.exe" "/c "\server B name\bat file folder\child_sftp.bat" at "", The process exit code was "1" while the expected was "0". End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:11:48 PM Finished: 5:11:49 PM Elapsed: 0.187 seconds. The package execution failed. The step failed.
But when tried to run .bat file manually in server B using command line then getting error as
C:\Windows\system32>\\server B name\bat file folder\child_sftp.bat
C:\Windows\system32>sftpg3 --user=id@xyz.ab.org -B "\server B name\bat file folder\child_sftp.bat"
sftp> bin
File transfer mode is now binary.
sftp> put "\server B name\file folder\abc.xlsx"
Error: Not connected.
sftp> rename abc.xlsx abc_todaydate.xlsx
Error: Not connected.
sftp> quit
Please help me out. what need to modify?
T.I.A