SFTP tools for Alteryx

[chrisha]

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]

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]

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]

@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

[mccartja]

Hi,

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

[byagelski]

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]

How exactly did you update the cryptography package?

[JakeS]

@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 

[AdrianWSH]

Have issues getting this to work on Alteryx 2021.2.1.

I managed to install the tool and can use it on the canvas but when I run the workflow with my credentials I hit the error attached. The packages in the requirements file have been installed and I have edited the file to reflect the most up-to-date versions.

Could anyone help me out?

[chrisha]

Hi @AdrianWSH ,

this is a weird bug since 2021.1, but hopefully easy to fix: 

Please download and install the pre-release through here: https://github.com/SKOPOS-ELEMENTS/SFTPTools_Alteryx/releases/tag/v1.0.2-alpha

After sucessfully installing the tool, close Alteryx Designer and add the following paths to your systems PATH variable (System settings -> Advanced Settings -> Environment Variables -> PATH -> Edit):

• C:\Program Files\Alteryx\bin\Miniconda3
• C:\Program Files\Alteryx\bin\Miniconda3\Scripts
• C:\Program Files\Alteryx\bin

If you are using the non-admin install of Alteryx, you need to use these paths:

• %USERPROFILE%\AppData\Local\Alteryx\bin\Miniconda3
• %USERPROFILE%\AppData\Local\Alteryx\bin\Miniconda3\Scripts
• %USERPROFILE%\AppData\Local\Alteryx\bin

When Alteryx is restarted, the tools should work properly again.

Best

Christopher