Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

SFTP tools for Alteryx

chrisha
11 - Bolide
Dear Alteryx community,
 
as we have used so many of your valuable feedbacks, solutions, and tools in the past three years, we would like to give something back and share some of our Alteryx tools we are using in our day-to-day work.
 
If you are like us using Alteryx for a lot of ETL heavy-lifting, you may have come across the need to download or upload files from/to SFTP servers. Alteryx' own Download tool supports FTP and SFTP connections, but is quite limited in its configuration. Most notably, you cannot use it to connect to servers where public/private key authentication is used.
Because we are relying heavily on such configurations, we developed two small Alteryx tools for connecting to SFTP servers using the Python SDK.
 
And today we would like to share these tools with the community. You can use the tools to easily upload and download files in your workflow. Either by writing files to a hard-drive or a network location or by using a blob field.
 
List of Features:
  • Connect to SFTP servers
  • Supports authentication using public/private key (with OpenSSH compatible key files)
  • Download files from an SFTP server
    • List all files in a remote directory, incl. file attributes
    • Download all files in a remote directory
    • Download files from a remote directory as specified in an input data stream
    • Download files to a local path or to a blob field in your workflow
    • Move or delete files on the server after successful download
  • Upload files to an SFTP server
    • Upload files from local or network resource by providing their file path
    • Upload files stored as blob fields in your data stream
    • Move or delete files after successful upload
You can find the latest release as an .yxi installer on our GitHub releases page: https://github.com/SKOPOS-ELEMENTS/SFTPTools_Alteryx/releases
 
If you are interested in the details you can browse, clone, and change the code in our GitHub repository: https://github.com/SKOPOS-ELEMENTS/SFTPTools_Alteryx
 
In case you encounter any bugs or if you are wondering about a new feature, feel free to open an issue: https://github.com/SKOPOS-ELEMENTS/SFTPTools_Alteryx/issues
 
For our fellow German Alteryx users, you can also find some information on our Journal: https://skopos-elements.de/datentransfer-mit-sftp-servern-in-alteryx/
 
We are looking forward to your feedback and hope that these tools help you when working with SFTP servers in Alteryx!
 
Best
Christopher
27 REPLIES 27
tanvimakhija
7 - Meteor

Hi @chrisha

 

I am getting the same error as  @AdrianWSH.

The version in my system is 2020.3.5.(Non Admin Designer).The file installed in my system is ELEMENTS_SFTPTools_v1.0.yxi

 

I am using this tool for 2 separate logins in which the only change between the 2 logins is change in the Username and Password. The Hostname and other settings remain the same. The tool works completely fine for one login but is giving this error when I change the server login details

 

Attaching the error screenshot for your reference.

Kindly let me if the solution remains the same in this case as well or if there is anything else I need to try

chrisha
11 - Bolide

Hi @tanvimakhija ,

 

your error messages look to me like a problem with the server - if you have changed username and password, I guess there might be a typo. The error messages indicate that the server has dropped the connection and that the downloader cannot download the files.

 

To me it seems not to be an issue with the tool.

 

Best

Christopher

 

dougperez
12 - Quasar

Hello @chrisha , I'm trying to use this tool but I'm getting this weird error:

dougperez_0-1624819016575.png

 

tanvimakhija
7 - Meteor

Hi @chrisha 

 

Thankyou for your response. However, there is no issue with the credentials. I am using the same credentials to login to the server via Filezilla as well. Also the tools works fine when I use the option 'LIST FILES' in the output settings

The error comes only when I output the file to a local path

Is there an option to increase maximum connection and timeout limit for this tool.

 

The file to be downloaded is pretty huge in size and I assume the timeout is happening due to long time taken for downloading the file.

Let me know your views and if there is any alternate solution for such errors

 

tanvimakhija_0-1624857697826.png

 

chrisha
11 - Bolide

@dougperez This should be the same issue as with the SSL bindings. Have you tried changing the PATH variable as described here? https://community.alteryx.com/t5/Alteryx-Designer-Discussions/SFTP-tools-for-Alteryx/m-p/773175/high...

 

@tanvimakhija I see. No, as of now, you cannot change the timeout for operations. I will have a look at the library and see what I can do. Unfortunately, this means I have no good workaround as of right now - sorry.

dougperez
12 - Quasar

Yes, I changed the PATH variables but the error still continues...

tanvimakhija
7 - Meteor

Hey @chrisha

I was trying to find a solution for the error that I was getting while using the tool.

For some context - The files I was trying to download are huge in size i.e. >1GB.

Apparently, paramiko library that is used in the tool sometimes gives errors for large files. I looked around google to see how to resolve that. 

There are 2 files 'sftp_client.py' and 'sftp_file.py' where a small change is needed and it can help with the issue

In sftp_client.py at line 678 data = reader.read(32768) and

in sftp_file.py line 59 MAX_REQUEST_SIZE = 32768

 

I tried changing these to 1024 and it worked. It takes a bit longer to complete the workflow execution but it did the job.

Source - https://stackoverflow.com/questions/12486623/paramiko-fails-to-download-large-files-1gb

 

However, this doesnt work all the time 

chrisha
11 - Bolide

Hi @dougperez ,

 

at first we weren't able to replicate the problem, but after an upgrade on a colleague's machine, the same error occurred. For her, the solution was to double check the PATHs -- please make sure to add the paths separated by semicolon (;) in PATH, ideally as system-wide variable.

 

If that doesn't help, you might need to install Visual C++ Redistributables 2015 (https://www.microsoft.com/en-us/download/details.aspx?id=48145). Usually, the libraries should be supplied by Alteryx, but maybe something went wrong during installation.

 

We did both of these and after updating the PATH variables, it worked for our colleague.

 

Best

Christopher

 

stephen_lindsay
6 - Meteoroid

Hi there,

thanks for doing this, its great and I can get it working no problem except... for larger files. anything over about 27MB seems to fail - I can see that the download failed as I get an email notification from the sftp server. The workflow is left running but essentially just hanging. There is a file in the download location but its only a partial file.  Any suggestions?

Thanks!

Stephen

HariReddy
5 - Atom

Hi @chrisha, I am trying to use this solution for uploading files from local system to to an SFTP server, 

but not clear how to configure  the private/public key and the file path using SFTP uploader file. Could you help me with detailed steps on how to configure the SFTP uploader to upload a file from local drive?  Not sure which node I need to use to provide as input, which is a flat file from my local system location in my case. 

 

Thank you,

Hari

Labels