Geeting error while writing csv file to FTP server

CloverDX Platform
1

Hi Team,

I am getting error while I am writing csv file to FTP server but using same FTP server i am able to send the files manually. I think i am facing issue with ETL universal data writer.

Please help to handle this.

Please find the below error message:

16:11:58,014 INFO [WatchDog_11] ---------------------------------** End of Log **--------------------------------
16:11:58,234 ERROR [WatchDog_11] Component [Write Sucess Log:WRITE_SUCESS_LOG] finished with status ERROR. (In0: 0 recs)
Component pre-execute initialization failed.
connection is closed by foreign host
16:11:58,236 ERROR [WatchDog_11] Error details:
org.jetel.exception.JetelRuntimeException: Component [Write Sucess Log:WRITE_SUCESS_LOG] finished with status ERROR. (In0: 0 recs)
at org.jetel.graph.Node.createNodeException(Node.java:631)
at org.jetel.graph.Node.run(Node.java:599)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: Element [WRITE_SUCESS_LOG:Write Sucess Log]-Component pre-execute initialization failed.
at org.jetel.graph.Node.run(Node.java:554)
… 3 more
Caused by: [attribute = fileURL]-java.io.IOException: com.jcraft.jsch.JSchException: connection is closed by foreign host
at org.jetel.util.MultiFileWriter.prepareTargets(MultiFileWriter.java:220)
at org.jetel.util.MultiFileWriter.init(MultiFileWriter.java:167)
at org.jetel.component.DataWriter.prepareWriter(DataWriter.java:298)
at org.jetel.component.DataWriter.preExecute(DataWriter.java:292)
at org.jetel.graph.Node.run(Node.java:552)
… 3 more
Caused by: java.io.IOException: com.jcraft.jsch.JSchException: connection is closed by foreign host
at org.jetel.component.fileoperation.pool.PooledSFTPConnection.getSession(PooledSFTPConnection.java:316)
at org.jetel.component.fileoperation.pool.PooledSFTPConnection.getSession(PooledSFTPConnection.java:252)
at org.jetel.component.fileoperation.pool.PooledSFTPConnection.connect(PooledSFTPConnection.java:271)
at org.jetel.component.fileoperation.pool.SFTPConnectionFactory.makeObject(SFTPConnectionFactory.java:30)
at org.jetel.component.fileoperation.pool.SFTPConnectionFactory.makeObject(SFTPConnectionFactory.java:21)
at org.jetel.component.fileoperation.pool.DefaultConnectionFactory.makeObject(DefaultConnectionFactory.java:39)
at org.jetel.component.fileoperation.pool.DefaultConnectionFactory.makeObject(DefaultConnectionFactory.java:26)
at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:1220)
at org.jetel.component.fileoperation.pool.ConnectionPool.borrowObject(ConnectionPool.java:91)
at org.jetel.util.protocols.AbstractURLConnection.connect(AbstractURLConnection.java:77)
at org.jetel.util.protocols.sftp.SFTPConnection.connect(SFTPConnection.java:97)
at org.jetel.util.protocols.sftp.SFTPConnection.getOutputStream(SFTPConnection.java:132)
at org.jetel.util.file.FileUtils.getOutputStream(FileUtils.java:1537)
at org.jetel.util.TargetFile.setOutput(TargetFile.java:642)
at org.jetel.util.TargetFile.setNextOutput(TargetFile.java:370)
at org.jetel.util.TargetFile.initOutput(TargetFile.java:554)
at org.jetel.util.TargetFile.init(TargetFile.java:169)
at org.jetel.util.MultiFileWriter.prepareSingleTarget(MultiFileWriter.java:236)
at org.jetel.util.MultiFileWriter.prepareTargets(MultiFileWriter.java:217)
… 7 more
Caused by: com.jcraft.jsch.JSchException: connection is closed by foreign host
at com.jcraft.jsch.Session.connect(Session.java:269)
at com.jcraft.jsch.Session.connect(Session.java:183)
at org.jetel.component.fileoperation.pool.PooledSFTPConnection.getSession(PooledSFTPConnection.java:301)
… 25 more

16:11:58,351 INFO [WatchDog_11] Execution of phase [1] finished with error - elapsed time(sec): 5
16:11:58,351 ERROR [WatchDog_11] !!! Phase finished with error - stopping graph run !!!
16:11:58,351 INFO [WatchDog_11] Final dictionary content:
16:11:58,351 INFO [WatchDog_11] DictEntry:ARLineFileName:string:{value=Invoice_Lines201610131810.csv}
16:11:58,351 INFO [WatchDog_11] DictEntry:ARDistFileName:string:{value=Invoice_Distribution201610131810.csv}
16:11:58,352 INFO [WatchDog_11] -----------------------** Summary of Phases execution **---------------------
16:11:58,352 INFO [WatchDog_11] Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
16:11:58,352 INFO [WatchDog_11] 0 FINISHED_OK 2 135259
16:11:58,352 INFO [WatchDog_11] 1 ERROR 2 140910
16:11:58,353 INFO [WatchDog_11] ------------------------------** End of Summary **---------------------------
16:11:58,353 INFO [WatchDog_11] WatchDog thread finished - total execution time: 5 (sec)
16:11:58,353 INFO [JobFinalizer_11] Finalization
16:11:58,355 INFO [JobFinalizer_11] RunTime: 5 secs
16:11:58,355 ERROR [JobFinalizer_11]
------------------------------------ Error details --------------------------------------
Component [Write Sucess Log:WRITE_SUCESS_LOG] finished with status ERROR. (In0: 0 recs)
Component pre-execute initialization failed.
connection is closed by foreign host
-----------------------------------------------------------------------------------------
16:11:58,356 INFO [JobFinalizer_11] Finished Status: ERROR

Thanks,
Jagan.

2

Hi jagan,

This issue may be caused by several reasons.

1. The port is not opened.
2. There is a user limit set on the FTP server.
3. You are using SFTP protocol instead of plain FTP.
4. There is a firewall on the FTP server or on your machine that is blocking the communication.

Please try the following.

1. Make sure you are using the correct port number.
2. Check whether you are using SFTP protocol to connect to a SFTP server.
3. Try saving the file on a different FTP server (via Designer).
4. Check the settings of the FTP server (user limit, etc.).
5. Temporarily turn off the firewall(s).

Please let me know about the outcome.

Best regards,

3

Hi Lukas Cholasta,

Please find my below comments,

1. Make sure you are using the correct port number. – I am using below URL to write the files and port is 22 but without port I am able to connect to server to select the files to read from Clover ETL.

sftp://${SFTPUserName}:${SFTPPassword}@${SFTPHost}/${SFTPOutputFile}

2. Check whether you are using SFTP protocol to connect to a SFTP server.-- Yes, I am using SFTP protocol while connecting through Winscp.
3. Try saving the file on a different FTP server (via Designer).-- Yes it is working with other SFTP servers.
4. Check the settings of the FTP server (user limit, etc.). – I am not sure about this, I am not a administrator to change the setting.
5. Temporarily turn off the firewall(s). – I am not a administrator turn off firewall.

I would like to describe about the issue.

> I am able to connect to SFTP server from ETL and select the files remotely and able to read files without any issues.
> I am facing issue when I am writing the file to SFTP server through ETL.
> using other tool(Winscp): I got the same kind of exception when i am writing the files to winscp. Please find the Winscp Exception.png for the exception.
> I did some analysis and finally I resolve the issue from Winscp by unchecked Preserve timestamp option. for more details please find the Winscp Setting.png.
> Now i am able to move the file to Winscp maually without any exception after I uncheck the Preserve timestamp option.

Similarly, Is there any option to handle this kind of exceptions in Clover ETL?

Please help me on this.

Thanks,
Jagan.

4

Hi,

I’ve consulted your issue with our developers and it is most probably in settings of the FTP server itself. The FlatFileWriter (or UniversalDataWriter) is using just a bare implementation of the java.net.URLConnection class to save the file to the FTP server. Also, while you’re creating the file and not copying it (and preserving it’s modification date), these issues seems to be unrelated. I’ve attached a simple console Java application that uploads a file with “Hello world!” content to a FTP server. Please try to upload a file to that FTP server with it. If this successes, we can start looking for an issue in our code.

I’d like to let you know that it’s perfectly safe to run this app. Following is its code.

package core;

import java.io.OutputStream;
import java.net.URL;
import java.net.URLConnection;

public class FTPWriter {

	public static void main(String[] args) throws Exception {
		if (args.length == 0) {
			System.out.println("usage: java -jar FTPWriter ftp://user:password@hostname:port/path/to/file.txt");
			return;
		}
		
		URL url = new URL(args[0]);
		URLConnection connection = url.openConnection();
		connection.setDoInput(false);
		connection.setDoOutput(true);
		
		try (OutputStream os = connection.getOutputStream()) {
			os.write("Hello world!".getBytes());
		}
	}
}

Thanks & regards,