Transfer Protocol: SFTP
Using WinScp .NET Assembly through SQL Server 2008 CLR
SQL Server Instance: Windows Server 2008 R2
CLR Version: v2.0.50727
I work in an enterprise environment and I used WinSCP .NET library to create a small .NET project that would allow me to connect to an enterprise FTP server and list contents of a remote directory. I was able to do that successfully on my desktop but, my overall goal was to list those contents as a table in an SQL Server environment. I added some additional logic to my .NET project so that it can be used as a CLR table valued function in SQL Server. I saved my .dll along with WinSCP.dll and winscp.exe on my SQL Server instance and I was able to successfully register both assemblies. For testing purposes I left ftp server connection information hard coded (this is the same connection information that i was able to connect with on my desktop)
When i run my .NET project as a CLR table valued function in SQL Server I get the error shown at the bottom of this post.
FTP.getRemoteDirInfo is my .NET function
Error happens on Session.Open(SessionOptions)
I'm able to ping the ftp server that I'm trying to connect from SQL Server instance and I'm able to manually FTP files using WinSCP from SQL Server instance.
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "TVF_FTP_List":
System.UriFormatException: Invalid URI: The URI is empty.
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
at WinSCP.ExeSessionProcess..ctor(Session session)
at WinSCP.Session.Open(SessionOptions sessionOptions)
at FTP.getRemoteDirInfo(String UsrName, String Passwrd, String Host, String FTPprotocol, String SSHFingerPrint, String RemoteDir)