ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

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

SFTP tools for Alteryx

chrisha
10 - Fireball
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
AndyM
Alteryx
Alteryx

Hi @chrisha ,  I was trying this solution for a customer as it seems like it is exactly what we need.  The customer needs to provide a Private key file to their vendor's SFTP Server to download a file.  The issue we ran into is that the tool gives an error on the key file - "Not a DSA-compliant key file".  They indicated they generated the key file using SSH-keygen so I thought it should work.  Any thoughts?

 

Thanks,

Andy 

chrisha
10 - Fireball

Hi Andy,

 

sorry, just came across your post here after you filed the issue over at GitHub.

 

The library we use should actually handle both RSA and DSA types of key files. And as far as I know the files we use in our projects have been created the same way. When googling for the message to narrow down the issue, I came across some posts saying they weren't able to connect to the server using ppk files and had to convert them to pem files.

 

There are some guides on how to do this: https://aws.amazon.com/premiumsupport/knowledge-center/convert-pem-file-into-ppk/

 

Maybe the conversion helps?

Another issue might be a password set for the keyfile, that is not provided in the Alteryx tool. That should produce another error message, but better safe than sorry.

 

Best

Christopher

 

JakeS
Alteryx
Alteryx

@chrisha 

 

Based on installation failures in Designer 2020.4 and higher, I believe that the cryptography package has to be updated in requirements.txt. I updated to most recent (3.4.6) and installation was successful. 

 

Thanks

 

Jake

Jake Samuels

Principal Support Engineer
Alteryx, Inc.
mccartja
5 - Atom

Hi,

 

This looks really useful. Can I run it in Alteryx Server?

byagelski
8 - Asteroid

Thank you for this!  We're using it to pull weekly files of gas card transactions for our vendor's ftp site.  It worked beautifully to grab them after the first try...of course that was after working a couple hours trying to get the download tool to work.

tr3nd
5 - Atom

How exactly did you update the cryptography package?

JakeS
Alteryx
Alteryx

@tr3nd 

 

The following steps should resolve the error:

 

1. Open the yxi as a zip file (If you have 7-zip installed, you can right click the yxi in file explorer and select "open archive")

2. Locate the folders for both the SFTP Upload and Download tools

3. Inside each tool folder should be a file named "requirements.txt"

4. Open the text files and change line "cryptography==2.7" to "cryptography==3.4.6" 

5. Save the files

6. Run the yxi 

 

 

Jake Samuels

Principal Support Engineer
Alteryx, Inc.
Labels