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

[XiaoDSJ]

How did you update the cryptography package?

[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!

[diegolondono70]

Hi,

It was the version of Alteryx.  I downgraded and it fixed it.

[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):¶  File "main.py", line 5, in <module>¶  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\pysftp\__init__.py", line 12, in <module>¶    import paramiko¶  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\__init__.py", line 22, in <module>¶    from paramiko.transport import (¶  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\transport.py", line 33, in <module>¶    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 <module>¶    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 <module>¶    from cryptography.exceptions import (¶  File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\exceptions.py", line 9, in <module>¶    from cryptography.hazmat.bindings._rust import exceptions as rust_exceptions¶ImportError: DLL load failed while importing _rust: The specified module could not be found.¶

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.

[yimiperazavasquez]

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?

[Drvt6713]

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 <module>
File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\pysftp\__init__.py", line 12, in <module>
import paramiko
File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\__init__.py", line 22, in <module>
from paramiko.transport import (
File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\paramiko\transport.py", line 33, in <module>
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 <module>
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 <module>
from cryptography.exceptions import (
File "C:\ProgramData\Alteryx\Tools\SKOPOS_SFTP_venv\Lib\site-packages\cryptography\exceptions.py", line 9, in <module>
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.