SQL Loader Connector sftp using ssh key files

[AroraA3 (Amardeep Singh Arora)]

Which IIQ version are you inquiring about?

8.3p3

Share all details about your problem, including any error messages you may have received.

We are trying to use SFTP protocol for SQLLoader connector which requires a username and password, we are trying to use ssh keys to authenticate instead of username password. Is there a way that we can achieve this? I know that delimited file connector has separate options for this.
I want to see if we can do something similar for SQL Loader.

The problem arises when we have multiple server architecture (multiple tasks and ui servers) we need to be able to specify the ssh key path to do sftp to a specific server to read and write to the file.

[ryan_toornburg]

Hi @AroraA3 - Unfortunately the current SQLLoader connector does not have this functionality.

[ismaelmoreno1 (Ismael Moreno)]

Hi @AroraA3

In this case, you can create a schedule task to download file via sftp protocol to task server. Once file is downloaded in your local environment you should execute Aggregation task of your SQLLoader application to read the file.

To download file via sftp protocol, you can use following code:

import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.io.FileOutputStream;
import java.io.InputStream;



String sftpHost = "sftp.example.com";
int sftpPort = 22;
String sftpUser = "your-username";
String privateKeyPath = "path/to/your/private/key";
String remoteFilePath = "/remote/path/to/file.txt"; 
String localFilePath = "path/to/local/file.txt";     

Session session = null;
ChannelSftp sftpChannel = null;

try {
    JSch jsch = new JSch();
    jsch.addIdentity(privateKeyPath);

    session = jsch.getSession(sftpUser, sftpHost, sftpPort);
    session.setConfig("StrictHostKeyChecking", "no"); de host
   
    session.connect();
    System.out.println("Connection SFTP established.");

    Channel channel = session.openChannel("sftp");
    channel.connect();
    sftpChannel = (ChannelSftp) channel;
    System.out.println("Chanel SFTP is open.");
   
    try (InputStream inputStream = sftpChannel.get(remoteFilePath);
         FileOutputStream fileOutputStream = new FileOutputStream(localFilePath)) {

        byte[] buffer = new byte[1024];
        int bytesRead;
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            fileOutputStream.write(buffer, 0, bytesRead);
        }

        System.out.println("File download succesfully: " + localFilePath);
    }

} catch (Exception e) {
    System.err.println("Error in transfer: " + e.getMessage());
    e.printStackTrace();
} finally {
    
    if (sftpChannel != null && sftpChannel.isConnected()) {
        sftpChannel.disconnect();
    }
    if (session != null && session.isConnected()) {
        session.disconnect();
    }
}
    

Please, let me know if this solution is valid for you, or let me know any additional question

[AroraA3 (Amardeep Singh Arora)]

Hi @ismaelmoreno1

Thanks for the snippet, I will try this, however, our major difficulty occurs when it comes to multiserver architecture. Even if i download the file using this code, when running the aggregation it will not know what server it downloaded the file on, also problem would be when we want to write back to the file during provisioning. We cannot specify which server the file is on while writing back.

[ismaelmoreno1 (Ismael Moreno)]

Hi @AroraA3

In this case, when you set url file in SQL Loader connector, it is possible set the IP address of the server, for example using the following line:

jdbc:csv:/\\IPaddress\folderpath

More details about url configuration: Configuration Parameters

So, you should set an IP address to configure your SQL Loader connector to read and provision the file, and download your file in the same server