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/releasesIf you are interested in the details you can browse, clone, and change the code in our GitHub repository: https://github.com/SKOPOS-ELEMENTS/SFTPTools_AlteryxIn 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/issuesFor 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!BestChristopher

Hi Christopher, Also posted on GitHub under Issues. After successfully installing the latest Pre-Release version of the tool to my installation of Alteryx Designer (2023.2.1.7) I received the same bug error. So excited to see your post for the bug fix (below) by adding the PATH variables however, after adding first the System variables, which did not fix the issue, and then the User variables, neither fixed the issue. I am still receiving the error below: Error: SFTP Downloader (20): Traceback (most recent call last): File "main.py", line 5, in File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\pysftp\__init__.py", line 12, in import paramiko File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\__init__.py", line 22, in from paramiko.transport import ( File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\transport.py", line 33, in from cryptography.hazmat.primitives.ciphers import algorithms, Cipher, modes File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\hazmat\primitives\ciphers\__init__.py", line 11, in from cryptography.hazmat.primitives.ciphers.base import ( File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\hazmat\primitives\ciphers\base.py", line 10, in from cryptography.exceptions import ( File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\exceptions.py", line 9, in from cryptography.hazmat.bindings._rust import exceptions as rust_exceptions ImportError: DLL load failed while importing _rust: The specified module could not be found. Please help as I am exhausting options to get my flow to upload to SFTP with SSH Key and was very excited to find your tool. Look forward to your response. Thanks.

Hello, I followed all the installation steps and I am having this problem, when I am going to create the system variables, what should I put in the name of the variable? alteryx_error.png

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

Download

Accepted answers

All comments

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

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.

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?

sftp-error.png

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

tanvimakhija

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

stephen_lindsay

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

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

nasyikin

Could you provide any workflow sample using the SFTP tools for downloader and uploaded.

diegolondono70

Hi,

I installed the tool on Alteryx 2023.1 and I get this error:

Error: SFTP Downloader (1): Traceback (most recent call last):
File "main.py", line 416, in pi_push_all_records
File "main.py", line 226, in build_ayx_output
File "main.py", line 176, in _list_to_recordinfo
RuntimeError: Invalid field size: '12345678'; DateTime field size must be between 19 and 38.

I reinstalled Alteryx and It not worked.

Shu-Pei

Have you found a solution to this? I also see the same error message.

Thank you!

Amadeu_gustavo

Hello @chrisha ,

Just installed this tool in version 2022.1.1 and got the followinf error in my first use:

	SFTP Downloader (2)	Traceback (most recent call last):&para;  File "main.py", line 5, in <module>&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\pysftp\__init__.py", line 12, in <module>&para;    import paramiko&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\__init__.py", line 22, in <module>&para;    from paramiko.transport import (&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\transport.py", line 33, in <module>&para;    from cryptography.hazmat.primitives.ciphers import algorithms, Cipher, modes&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\hazmat\primitives\ciphers\__init__.py", line 11, in <module>&para;    from cryptography.hazmat.primitives.ciphers.base import (&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\hazmat\primitives\ciphers\base.py", line 10, in <module>&para;    from cryptography.exceptions import (&para;  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\exceptions.py", line 9, in <module>&para;    from cryptography.hazmat.bindings._rust import exceptions as rust_exceptions&para;ImportError: DLL load failed while importing _rust: The specified module could not be found.&para;

I don't really know much python, but already pip installed cryptography and paramiko libs.

Is there anything else I should do?

Thanks since now.

pmacouzet75

Hello Chrisha, I think the installation completed without errors, but I can't find the new tools. Can you tell me where to find them?

Quick Links

This months top contributors

mceleavey 382

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262