November 13, 2019 at 7:52 pm
Hi,
I've inherited the maintenance of SSIS packages that were created by our former developer so I'm not really familiar with things in this area. We are currently using ftp in some of the packages and now are going to go to sftp, but the packages cannot connect using the same credentials. We see in the logs that it passes the credential check but does not connect. Someone suggested maybe installing an FTP/SFTP Extension for Visual Studio. Has anyone else run into this problem? We are using Visual Studio Professional 2015 and the current FTP connects with no problems. Thank you in advance! 🙂
[2019-11-13 10:34:29]:CONNECT [ 9519] - Native user 'starkfriededi' authenticated
[2019-11-13 10:34:29]: REPLY [ 9519] - [starkfriededi] 230 Password Ok, User logged in
[2019-11-13 10:39:09]:CONNECT [ 9523] - Shell access is not permitted
[2019-11-13 10:39:09]:CONNECT [ 9523] - The client closed the connection
[2019-11-13 10:39:09]:CONNECT [ 9523] - Connection terminated
Thanks!
Bea Isabelle
November 13, 2019 at 8:05 pm
If you don't mind spending some cash on a 3rd party product, Cozyroc's SSIS add-ons will help you do the job (and many others).
Take a look here & track down the File Transfer Task.
Alternatively, with a bit more code (but less cash), you will probably be able to get a Winscp solution up and running.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 13, 2019 at 8:10 pm
Hi Phil,
Thanks for your reply! So does Visual Studio not work with sftp? Right now the SSIS package just has the ftp setup with the connection manager and it works fine. The only thing i did was change the server to sftp and port to 22 and it will not connect.
Thanks!
Bea Isabelle
November 13, 2019 at 8:28 pm
Hi Phil,
Thanks for your reply! So does Visual Studio not work with sftp? Right now the SSIS package just has the ftp setup with the connection manager and it works fine. The only thing i did was change the server to sftp and port to 22 and it will not connect.
Unfortunately, there is no out-of-the-box support for SFTP in native SSIS.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 13, 2019 at 8:44 pm
SSDT does not have a built-in sFTP component - the only way to utilize sFTP is to utilize a third-party product. The basic setup would be an Execute Process Tasks - which calls the WinSCP (or other client's) command line with the appropriate parameters.
Here is an outline from WinSCP for a basic configuration: https://winscp.net/eng/docs/guide_ssis
With that said - WinSCP also has a .NET assembly that can be called from a Script Task. From the above link - or here: https://winscp.net/eng/docs/library_ssis - they outline this as the preferred approach.
I have implement both approaches - and either works. I prefer the second approach though...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 13, 2019 at 10:02 pm
Hi,
Thank you for the information. I will look at both the links you provided and keep digging. 🙂
Thanks!
Bea Isabelle
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply