The portable executables (near the top of the downloads page) are the easiest to use, as you simply drop the two executables into a folder and start using them without going through an installation process. It has both a UI and a command-line component, though I use the latter exclusively for this solution. This is a mature, well-documented, and free (GPL) SFTP client. To develop and test this solution, I use just a couple of tools: Therefore, my go-to solution for consuming SFTP with SSIS is the last one on the list above, which is the solution I describe herein. In my work as a consultant, I’ve found that the easiest solution – when considering not just the technical components but the administrative work – is to use a command-line approach leveraging a mature and well-documented SFTP client. For coding solutions using external DLLs, often the same issues exist because the external DLLs have to be registered on each server, which is an easy enough process but surprisingly difficult to get approved in many cases. For example, using a third-party SSIS task provides a more streamlined approach (often requiring no manual coding at all), but often – especially in larger organizations – installing a third-party component requires so much extra testing and administrative approval that it makes it more trouble than its worth. I’ve used each of these solutions in the past, and each one has its own merits and risks.
However, it is possible to build your own solution using free tools and a bit of code. There is a built-in FTP task in Integration Services, but it is very limited in its functionality and will not connect to an SFTP server. If you’ve used SSIS for any significant amount of time, you’ve almost certainly run into this roadblock: You need to download a file stored on an SFTP (secure FTP) server, but connecting to SFTP with SSIS is not natively supported.
0 kommentar(er)
