Use SFTP (WinSCP) within SSIS


Download WinSCP from: http://winscp.net/eng/download.php

You can add a Script Component in your SSIS package and use the sample code below:

public void Main()
{
// TODO: Add your code here
try
{

const string logname = “E:\\myFolder\\ftp_log.xml”;
//// Run hidden WinSCP process
Process winscp = new Process();
winscp.StartInfo.FileName = “C:\\Program Files (x86)\\WinSCP\\winscp.com”;

winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;
winscp.StartInfo.UseShellExecute = false;
winscp.StartInfo.RedirectStandardInput = true;
winscp.StartInfo.RedirectStandardOutput = true;
winscp.StartInfo.CreateNoWindow = true;
winscp.Start();

//// Feed in the scripting commands
winscp.StandardInput.WriteLine(“option batch abort”);
winscp.StandardInput.WriteLine(“option confirm off”);
winscp.StandardInput.WriteLine(“open myUser@sftp.mysftpsite.com”);
winscp.StandardInput.WriteLine(“put E:\\myFolder\\data\\myFiles.txt”);

winscp.StandardInput.WriteLine(“mv myFiles.txt myNewFile_” + System.DateTime.Now.Month.ToString() + “_” + System.DateTime.Now.Day.ToString() + “_” + System.DateTime.Now.Year.ToString() + “.csv”);
winscp.StandardInput.Close();

// Collect all output (not used in this example)
string output = winscp.StandardOutput.ReadToEnd();

// Wait until WinSCP finishes
winscp.WaitForExit();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception xxx) {
Dts.TaskResult = (int)ScriptResults.Failure;
}

}
}

From the WinSCP login client UI, you use myUser/mypass to connect your site and save your connection information as this:
myUser@sftp.mysftpsite.com
And your package should be ready to go. You can go WInSCP website to find other configuration as well.
The last setup is to make sure the package can run as an agent job. What I did was to connect to my ftp site and saved the credentials (user/pass) on my local machine and export the settings from WinSCP:>>Tools>>Export/backup Configuration to WinSCP.ini file and I put this file to WinSCP root folder and gave the service account user access permission to access this folder. You can modify path information within this WinSCP.ini file.
Don’t forget to give the permission to the data folder for the account who is running the agent service!

After create the account, from Edit>>Advanced>>Directories check Remote directory and Local directory (working folder for the file).

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s