Using WinSCP and SSIS to Download files from SFTP

Recently a client of the company I work for required us to download files using their SFTP server and process these files and for that we decided that we would use SSIS to complete the task.

SSIS provides functionality to connect to a FTP server but not a SFTP server. Luckily SSIS allows you to execute a process (in our case WinSCP), which we are going to use to download the files.

To complete this task you will need;

  • A copy of WinSCP
  • SQL Server Data Tools
  • A Script.txt File (Explained Later)

Firstly, you will need to open SQL Server Data Tools and create a new “Integration Services Project”. Once this has opened you need to drag a “Execute Process Task” onto the Control Flow area.

Right click on the task and click on Edit, this will bring up the “Execute Process Task Editor”. In the Editor you have the following options.

  • General
    • Name
    • Description
  • Process
    • RequireFullFileName
    • Executable
    • Arguments
    • WorkingDirectory
    • StandardInputVariable
    • StandardOutputVariable
    • StandardErrorVariable
    • FailTaskIfReturnCodeIsNotSuccessValue
    • SuccessValue
    • TimeOut
    • TerminateProcessAfterTimeOut
    • WindowStyle
  • Expressions
    • Expressions

We will be amending the options in bold.

Set both the Name and Description Property to “Download from SFTP” .

The Executable property should be set to the location of the WinSCP install, in my case it was “C:\Program Files (x86)\WinSCP\WinSCP.exe”.

The Arguments property needs to be set to “-script=c:\Script.txt”. This is the location of a text file which holds the commands which WinSCP will execute. The script file I used is posted below.

option batch abort
option confirm off
open sftp://<User>:<Password>@<IPAddress>/ -hostkey=<HostKeyHere> --- e.g. stfp://root:password@123.456.789.2/ -hostkey="ssh-rsa AA:BB:CC:12:22"
get <PathOnServer>/<FileName> c:\<PathOnLocal> --- e.g. /home/Uploads/File.txt c:\SFTPDownload
exit

Finally, the Working Directory should be set to the folder which WinSCP can be found, mine is set to “C:\Program Files (x86)\WinSCP”.

You should then be able to execute the task and the files which you have set in the Script to be downloaded, will be downloaded into the specified path.

Read More

Reflection

I recently came across an issue where I needed to load a particular Windows Form dependant on the result of a SQL query. After some amount of time googling and asking a question on StackOverflow, I finally found the answer in the System.Reflection namespace.

This post will explain the methods I used to load the required form.

The System.Reflection namespace examines metadata to retrieve information about assemblies, modules and members etc. Below is the code I used to achieve what was required.

//Retrieve form name from SQL database. 
string formName = dr["formname"].ToString(); 
string assemblyName = Assembly.GetExecutingAssembly().GetName().Name; 
string componentName = assemblyName + "." + formName; 
Type type = Type.GetType(componentName, false, true); 
ConstructorInfo ci = type.GetConstructor(new Type[0] { }); 
object[] argVals = new Object[] { }; 
Form form = (Form)ci.Invoke(argVals); 
form.Show();

I will now proceed to explain each line of code and what it does when executed.

Firstly I retrieved the form name that I needed to open from the SQL database and placed it into a variable called “formName”. This will be used later on to build the “componentName” to pass to the “GetType” method.

I then declared a variable called “assemblyName” which stores the name of the assembly of the code that is currently executing.

string assemblyName = Assembly.GetExecutingAssembly().GetName().Name;

I then proceeded to build the “componentName” which combines the “assemblyName” and the “formName”, which will now be passed to the “GetType” method. The result of the “componentName” within my project will be – “Reflection.Form2”. “Reflection” is the namespace of the project and “Form2” is the name of the form.

The next line Type type = Type.GetType(componentName, false, true); sets a variable names “type” to the type of the “componentName” variable which was set earlier on. The other two parameters that are passed are “throwOnError” and “ignoreCase”.

The “ConstructorInfo” line provides access to the constructor metadata and allows us to configure the form to accept information such as parameters on execution by setting the type of parameter to expect.

The “argVals” array allows us to store the parameters or information that needs to be passed to the opening form.

Form form = (Form)ci.Invoke(argVals); The code to the left sets a variable of the type Form to the form we wish to open using the reflection code above.

Lastly “form.Show()” instructs the application to open.

Read More

Hello World…

Hi,

Welcome to my new blog. I decided to setup this blog to document my learnings throughout my career as a .NET developer. Over the last couple of years I have been thrown in the deep end with my job and was tasked with teaching myself ASP.NET, C#, WinForms, SQL and much more.

I want to use this blog as a reference for myself but also to help others who have faced the same challenges that I have. At present I am working my way through the MCPD certification for Visual Studio 2010, I have one exam left (70-519 Pro Exam). Once I have completed this certification set I plan to go onto the MCSD, developing Windows Apps, and will then continue to complete a variety of certifications.

I have been following the blogs of quite a few high profile members of the developer community, such as Iris Classon and Scott Hanselman, and I hope that one day my blog and social presence will be as successful as theirs!

That’s all from me for now, I plan to post my first proper post in the coming days.

Read More