question

SQL USER avatar image
SQL USER asked

script to copy files from sftp

I want to connect to sftp server and transfer a file using SSIS.

I tried the ftp connection and it fails.

How can I do this using a script task.

I'm not very good with scripts. Can you provide me with a working script?

Thanks,

connectionscript-taskftpsftp
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered

Unsurprisingly, the windows command also lacks native support for sftp. You're going to need a 3rd party tool either way.

I hate answers that are just links, but here is a step-by-step tutorial on setting up an Execute Process SSIS task to retrieve a file from an sftp server - https://www.mssqltips.com/sqlservertip/3435/using-sftp-with-sql-server-integration-services/

You can do the same thing with any other secure transfer command line tool you choose.

For a native SSIS experience, there are also some 3rd party tools that add an SFTP task directly within SSIS. TaskFactory by sentry one is one that I have used (https://www.sentryone.com/products/task-factory/feature/sftp-ssis-secure-ftp) but cozyroc makes another widely used SSIS task for transferring files.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.