SQL Server SSIS 2016 CmDExec Stage PSFTP command returns error code 1 instead of 0
Greetings,
I have SQL SERVER 2016 Standard. Included in this I have SSDT 2016, and SSIS to go along with it.
Inside my Package, I have the following:
Command Exec Task:
Command: C:/Program Files/Putty/PSFTP.exe
Arguments: XXXXXX@sftp1.XXXX.com -pw XXXXX -be -batch -b "D:\Code\XXXXX\XXXXXX.bat"
Working Folder: D:\Data\ZIPFOLDER
Inside SSDT, this works without failure. When deployed to SQL SERVER, I recieve:
The process exit code was "1" while the expected was "0".
This happens whether or not there's files to get on the other side.
My steps to rectify:
1. I created a Windows SQL User: DB\SSISWORKUSER
2. I made DB\SSISWORKUSER an administrative-level user on the system.
3. I added DB\SSISWORKUSER on as FULL CONTROL to both C:\PROGRAM FILES\PUTTY and D:\ (the whole drive)
4. I confirmed that DB\SSISWORKUSER has FULL CONTROL of all fines inside the folders specified in step 3.
5. ON SQL SERVER, I gave DB_OWNER privileges on ALL databases, to include SSIS, My Database, MASTER, MSDB, and SSIS (I'll take away some things when I solve this)
6. I created a credential based DB\SSISWORKUSER
7. I created a Proxy User off of DB\SSISWORKUSER's credential and added CmdExec privileges, plus all SSIS privileges to the Proxy User.
8. I changed my deployed SSIS Job to be owned by the new Proxy.
9. I added ftp:\C, ftp:\D, and ftp:\E to the Internet Options Safe list.
I am still having difficulty getting this task to operate. It is still giving me the same error.
Any Idea on how to get this to work would be greatly appreciated.
I have SQL SERVER 2016 Standard. Included in this I have SSDT 2016, and SSIS to go along with it.
Inside my Package, I have the following:
Command Exec Task:
Command: C:/Program Files/Putty/PSFTP.exe
Arguments: XXXXXX@sftp1.XXXX.com -pw XXXXX -be -batch -b "D:\Code\XXXXX\XXXXXX.bat"
Working Folder: D:\Data\ZIPFOLDER
Inside SSDT, this works without failure. When deployed to SQL SERVER, I recieve:
The process exit code was "1" while the expected was "0".
This happens whether or not there's files to get on the other side.
My steps to rectify:
1. I created a Windows SQL User: DB\SSISWORKUSER
2. I made DB\SSISWORKUSER an administrative-level user on the system.
3. I added DB\SSISWORKUSER on as FULL CONTROL to both C:\PROGRAM FILES\PUTTY and D:\ (the whole drive)
4. I confirmed that DB\SSISWORKUSER has FULL CONTROL of all fines inside the folders specified in step 3.
5. ON SQL SERVER, I gave DB_OWNER privileges on ALL databases, to include SSIS, My Database, MASTER, MSDB, and SSIS (I'll take away some things when I solve this)
6. I created a credential based DB\SSISWORKUSER
7. I created a Proxy User off of DB\SSISWORKUSER's credential and added CmdExec privileges, plus all SSIS privileges to the Proxy User.
8. I changed my deployed SSIS Job to be owned by the new Proxy.
9. I added ftp:\C, ftp:\D, and ftp:\E to the Internet Options Safe list.
I am still having difficulty getting this task to operate. It is still giving me the same error.
Any Idea on how to get this to work would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you SO much....
it was the hostkey. In spite of the fact that I have been using filezilla to certify the host, but psftp did not know it.
it's running like a champ now!
Thanks!
it was the hostkey. In spite of the fact that I have been using filezilla to certify the host, but psftp did not know it.
it's running like a champ now!
Thanks!
ASKER