{"id":665,"date":"2020-04-27T19:19:57","date_gmt":"2020-04-27T19:19:57","guid":{"rendered":"http:\/\/markedcode.com\/?p=665"},"modified":"2025-03-03T13:44:34","modified_gmt":"2025-03-03T13:44:34","slug":"how-to-access-sftp-with-ssis-using-ssh-net","status":"publish","type":"post","link":"https:\/\/markedcode.com\/index.php\/2020\/04\/27\/how-to-access-sftp-with-ssis-using-ssh-net\/","title":{"rendered":"How to access SFTP with SSIS using SSH.NET"},"content":{"rendered":"

How to access SFTP with SSIS using SSH.NET. I have been looking around for an all encompassing guide to setting up and connecting to an SFTP server with SSIS and have been unable to find such an article. After some trial and error, I have put together how I was able to accomplish accessing an SFTP server with SSIS using SSH.NET by Renci.<\/p>\n

Configure SSIS<\/strong><\/h2>\n

\u2981 Make new SSIS Project
\n\u2981 Next, add script tag to control flow
\n\u2981 Then, edit script
\n\u2981 Right click References and select \u2018Manage NuGet Packages\u2019<\/p>\n

\"How<\/p>\n

\u2981 After that, go to Browse and find the following to packages and versions
\n\u2981 SSH.NET by Renci \u2013 2016.0.0
\n\u2981 SshNet.Security.Cryptography by Renci \u2013 1.2.0<\/p>\n

\"Needed<\/p>\n

\u2981 Right click on the project and select properties \u2013 I am using 4.5
\n\"c#
\n\u2981 Add the following name space to the script
\n\u2981 using Renci.SshNet;
\n\u2981 Add the following code to connect to the server and print out the files in the desired directory<\/p>\n

Code for SFTP connection with SSH.NET<\/strong><\/h2>\n

public void Main()<\/strong>
\n{<\/strong>
\n\u00a0 \/\/ SFTP credentials \u2013 smart to make these SSIS variables and read them in<\/strong>
\n\u00a0 var host = “host.com”; \/\/No \u201csftp:\/\/\u201d prior to host<\/strong>
\n\u00a0 var port = 2222;<\/strong>
\n\u00a0 var username = “user”;<\/strong>
\n\u00a0 var password = “pass”;<\/strong><\/p>\n

\u00a0 \/\/Directories<\/strong>
\n\u00a0 var remoteDir = “\/”; \/\/This is the desired remote directory<\/strong><\/p>\n

\u00a0 \/\/Create client with credientials<\/strong>
\n\u00a0 using (var client = new SftpClient(host,port,username,password))<\/strong>
\n\u00a0 {<\/strong>
\n\u00a0 Console.WriteLine(“Connect to ” + host + ” as ” + username);<\/strong>
\n\u00a0 client.Connect();<\/strong>
\n\u00a0 Console.WriteLine(“Connected.”);<\/strong><\/p>\n

\u00a0 \/\/List files in target server directory<\/strong>
\n\u00a0 var files = client.ListDirectory(remoteDir);<\/strong><\/p>\n

\u00a0 Console.WriteLine(“Files in ” + remoteDir + “: “);<\/strong><\/p>\n

\u00a0 \u00a0foreach (var file in files)<\/strong>
\n\u00a0 {\u00a0<\/strong>
\n\u00a0 \u00a0 if (client.Exists(remoteDir + file.Name)) \/\/Not needed<\/strong>
\n\u00a0 \u00a0 {<\/strong>
\n\u00a0 \u00a0 \u00a0 \u00a0Console.WriteLine(“Exists: ” + file.Name);<\/strong>
\n\u00a0 \u00a0 \u00a0}<\/strong>
\n\u00a0 }<\/strong>
\n}<\/strong><\/p>\n

Dts.TaskResult = (int)ScriptResults.Success; <\/strong>
\n}<\/strong><\/p>\n

Great,\u00a0 now we have all the base SSIS and C# looking good let\u2019s try to run it!
\nOh no! We get an error in the script!<\/p>\n

Error: Could not load file or assembly ‘Renci.SshNet, Version=2016.1.0.0, Culture=neutral, PublicKeyToken=1cee9f8bde3db106’ or one of its dependencies. The system cannot find <\/strong>
\nthe file specified.<\/strong><\/p>\n

Install packages to GAC manually<\/strong><\/h2>\n

For some reason, SSIS does not automatically see the packages you installed. We need to manually download the dll\u2019s now for our referenced packages and install them to the GAC (Global assembly cache). Go to the following links and download the packages.<\/p>\n

\"How
\nhttps:\/\/www.nuget.org\/api\/v2\/package\/SSH.NET\/2016.0.0<\/a>
\nhttps:\/\/www.nuget.org\/packages\/SshNet.Security.Cryptography\/1.2.0<\/a><\/p>\n

Once they are downloaded, we can unzip the packages with 7-Zip. In the Downloaded package, ssh.net.2016.0.0 > lib > netstandard1.3, I can see the dll that I want to manually add to the GAC.<\/p>\n

\"Local
\nOpen \u2018Developer Command Prompt for VS 2017\u2019 \u2013 and Run as Admin
\n\"Dev
\nAfter that, run the following command for both packages
\ngacutil \/i [PATH TO DLL] \/f
\n\"How
\nNow when we run our script we can see that the packages get recognized! In conclusion, we need to install the packages with NuGet, write our code to connect, then manually install the same versions of the dll’s so SSIS can see the packages we wish to use.<\/p>\n

Now that we know how to access SFTP with SSIS using SSH.NET, we can write our script to pull files, check if files exist, delete files, push files, and more.<\/p>\n

For more SSIS development blogs, read on!<\/a><\/p>\n

For technical questions, please visit https:\/\/d365techsupport.com<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

How to access SFTP with SSIS using SSH.NET. I have been looking around for an all encompassing guide to setting up and connecting to an SFTP server with SSIS and have been unable to find such an article. After some trial and error, I have put together how I was able to accomplish accessing an<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[7,20],"tags":[21],"class_list":{"0":"post-665","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-d365","7":"category-ssis","8":"tag-how-to-access-sftp-using-ssh-net-renci-ssis-c-code-microsoft-sql"},"_links":{"self":[{"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/comments?post=665"}],"version-history":[{"count":3,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":1338,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/posts\/665\/revisions\/1338"}],"wp:attachment":[{"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/media?parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/categories?post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/markedcode.com\/index.php\/wp-json\/wp\/v2\/tags?post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}