Interesting article, but I chose a slightly different path in handling things. I am using the portable version of WinSCP, meaning there is no installation needed. They also expose Command Line arguments, meaning that all the stuff stored in the batch file is passed along as variables in the package. So I can store the variables in a database somewhere, call them when the package loads, then pass them along to the command line options. Consider the following:(written in C#...)

//Attaching the debugger in a 64-bit instance of SSIS will not work.

//MessageBox.Show("Attach the Debugger", "Attach the Debugger");

// TODO: Add your code here

ScriptResults TaskResult;

//jf 04132010 Created objects to hold variables set at package level

String WinSCPPath = Dts.Variables["WinSCPPath"].Value.ToString();

String WinSCPCom = Dts.Variables["WinSCPCom"].Value.ToString(); ;

String WinSCPini = Dts.Variables["WinSCPini"].Value.ToString();

String WinSCPLoggingPath = Dts.Variables["WinSCPLoggingPath"].Value.ToString();

String WinSCPLogFileName = Dts.Variables["WinSCPLogFileName"].Value.ToString();

String LoginName = Dts.Variables["LoginName"].Value.ToString();

String LoginPassword = Dts.Variables["LoginPassword"].Value.ToString();

String SiteURI = Dts.Variables["SiteURI"].Value.ToString();

String HostKey = Dts.Variables["HostKey"].Value.ToString();

String FileToPush = Dts.Variables["FileToPush"].Value.ToString();

String UploadDirectory = Dts.Variables["UploadDirectory"].Value.ToString();//UploadDirectory

//jf 04132010 Created object to hold reference for FireAgain of the FireProgress Control

bool fireAgain = false;

try

{

//jf 04132010 Attach winscp to the process within the Package

System.Diagnostics.Process winscp = new System.Diagnostics.Process();

Dts.Events.FireProgress("WinSCP process Created", 0, 0, 100, "WinSCP", ref fireAgain);

//jf 04132010 Pass variables into the Process object

//Tell the Process where to find the WinSCP object for loading

winscp.StartInfo.FileName = String.Format("{0}{1}"

, WinSCPPath

, WinSCPCom);

//Pass the location of the ini file and the logging xml file to the Process

winscp.StartInfo.Arguments = String.Format("/log={3}{1}"

, WinSCPPath

, WinSCPLogFileName

, WinSCPini

, WinSCPLoggingPath);

Dts.Events.FireProgress("Assigned Logging File", 10, 0, 100, "WinSCP", ref fireAgain);

//Do not launch a new shell "cmd" window

winscp.StartInfo.UseShellExecute = false;

//Redirect the input and output to the Logging Component

winscp.StartInfo.RedirectStandardInput = true;

winscp.StartInfo.RedirectStandardOutput = true;

//Do not show a cmd window

winscp.StartInfo.CreateNoWindow = true;

//Start the Process Thread

winscp.Start();

Dts.Events.FireProgress("WinSCP process Launched", 20, 0, 100, "WinSCP", ref fireAgain);

//Pass configuration information to WinSCP

winscp.StandardInput.WriteLine("option batch abort");

winscp.StandardInput.WriteLine("option confirm off");

//Pass Login information to WinSCP for Authentication

winscp.StandardInput.WriteLine(String.Format("open {0}:{1}@{2} -hostkey=\"{3}\""

, LoginName

, LoginPassword

, SiteURI

, HostKey));

Dts.Events.FireProgress("Remote SFTP Connection Established", 40, 0, 100, "WinSCP", ref fireAgain);

//Execute WinSCP Commands

winscp.StandardInput.WriteLine("ls");

//--change to the needed directory

winscp.StandardInput.WriteLine(String.Format("cd {0}", UploadDirectory));

Dts.Events.FireProgress("Remote SFTP File Action", 60, 0, 100, "WinSCP", ref fireAgain);

//--List the directory

winscp.StandardInput.WriteLine("ls");

//--push the file to the server

winscp.StandardInput.WriteLine(String.Format("put {0}", FileToPush));

Dts.Events.FireProgress("Process Finished", 80, 0, 100, "WinSCP", ref fireAgain);

Dts.Events.FireProgress(String.Format("put {0}", FileToPush), 0, 0, 0, "WinSCP", ref fireAgain);

//Close input to the file

winscp.StandardInput.Close();

Dts.Events.FireProgress("Remote SFTP Disconnected, WinSCP Closed", 100, 0, 100, "WinSCP", ref fireAgain);

//wait for WinSCP to Exit

winscp.WaitForExit();

MessageBox.Show(winscp.StandardOutput.ReadToEnd(), "WinSCP Output");

TaskResult = ScriptResults.Success;

}

catch (Exception e)

{

//If there is problem, fail the event and output the exception Message...

Dts.Events.FireError(999, e.Message, "WinSCP", string.Empty, 0);

TaskResult = ScriptResults.Failure;

//MessageBox.Show(e.Message);

}

Dts.TaskResult = (int)TaskResult;

Dts.TaskResult = (int)TaskResult;

Notice that we can also fire events that bubble up a message within the context of the package. This means no third party installation on the server, no batch file to be overwritten and most important of all, everything is saved in a script task in the package.