I have an SSIS package that use to use an FTP connection manager. However I have to update the package using SFTP connection now. I know there exists a WinSCP option but the server which it will be hosted on will not have WinSCP, and from my knowledge the SSIS package can not have a bin folder with WinSCP. Any work arounds?
Asked
Active
Viewed 5,364 times
1
-
1I don't see how using reflection is going to solve your sftp issue. Reflection is just a mechanism for inspecting and invoking code. Sure, it's accurate but it's just as accurate to have told you "all you have to do is use some code" technically correct but completely useless. – billinkc Sep 10 '15 at 14:57
-
Ty @billinkc I felt like telling me to use reflection was a run around. – Angel Cloudwalker Sep 10 '15 at 15:28
-
For details how to use WinSCP from SSIS, see [SFTP Task for SSIS](http://winscp.net/eng/docs/guide_ssis) and [Using WinSCP .NET Assembly from SQL Server Integration Services (SSIS)](http://winscp.net/eng/docs/library_ssis). – Martin Prikryl Sep 11 '15 at 05:58
2 Answers
0
If WinSCP is not available then your other option is to obtain a third party Integration Services component that supports SFTP, for example:
Nathan Griffiths
- 12,277
- 2
- 34
- 51
0
We ended up doing a work around and used a BCP command via xp_cmdshell. All the security team had to do was give the SQL Server windows account read permission to the Server that had the flat file. So instead of using a SSIS we just scheduled a stored procedure.
Angel Cloudwalker
- 2,015
- 5
- 32
- 54