SSIS FTPS File handling

The following scripttask showcases both the FTP and FTPS based connections for uploading a file (it can be modified to perform more options). My earlier approach was to leverage the FTPWebRequest but the "AUTH SSL" command was taking a huge amount of time so decided to fall back on using WinSCP instead. Also ensure that the WinSCP.exe is added to the path environment variable and the WinSCP.dll is placed in the GAC.
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
/* * Traditional FTP */
/*ConnectionManager mgr = Dts.Connections.Add("FTP"); try { mgr.Properties["ServerName"].SetValue(mgr, "ftp://"+Dts.Variables["strFTPHost"].Value.ToString()); mgr.Properties["ServerUserName"].SetValue(mgr, Dts.Variables["strFTPUserName"].Value.ToString()); mgr.Properties["ServerPassword"].SetValue(mgr, Dts.Variables["strFTPPassword"].Value.ToString()); mgr.Properties["ServerPort"].SetValue(mgr, Dts.Variables["strFTPPort"].Value.ToString()); mgr.Properties["Timeout"].SetValue(mgr, Dts.Variables["strFTPTimeout"].Value.ToString()); mgr.Properties["UsePassiveMode"].SetValue(mgr, true); mgr.ConnectionString = Dts.Variables["strFTPHost"].Value.ToString() + ":" + Dts.Variables["strFTPPort"].Value.ToString(); //string conn = Dts.Connections["FTP Connection Manager"].ConnectionString; FtpClientConnection ftpConn = new FtpClientConnection(mgr.AcquireConnection(null)); ftpConn.Connect(); string[] fileLocation = new string[1]; fileLocation[0] = Dts.Variables["strLocalDirectory"].Value.ToString()+"\\"+Dts.Variables["strLocalFile"].Value.ToString(); ftpConn.SendFiles(fileLocation, Dts.Variables["strRemotePath"].Value.ToString(), true, false); ftpConn.Close(); } catch (Exception exp) { Dts.Variables["strExceptions"].Value = exp.Message + ":" + exp.StackTrace; }*/
/* * FTPS using WinSCP */
try { string file = Dts.Variables["strLocalDirectory"].Value.ToString() + "\\" + Dts.Variables["strLocalFile"].Value.ToString();
string username = Dts.Variables["strFTPUserName"].Value.ToString();
string password = Dts.Variables["strFTPPassword"].Value.ToString();
//string.Format("Running sync with {0}", username).Dump(DateTime.Now.ToString());
// Setup session options SessionOptions sessionOptions = new SessionOptions {
HostName = Dts.Variables["strFTPHost"].Value.ToString(),
UserName = username,
Password = password,
Protocol = Protocol.Ftp,
PortNumber = Dts.Variables["strFTPPort"].Value.ToString(),
FtpMode = FtpMode.Passive,
FtpSecure = FtpSecure.Implicit,
TlsHostCertificateFingerprint = Dts.Variables["strFTPFingerprint"].Value.ToString()
};
using (Session session = new Session()) {
session.ExecutablePath = Dts.Variables["strWinSCPExecutable"].Value.ToString();
// Connect session.Open(sessionOptions);
// Upload files
TransferOptions transferOptions = new TransferOptions();
transferOptions.TransferMode = TransferMode.Binary;
TransferOperationResult transferResult;
transferResult = session.PutFiles(file, Dts.Variables["strRemotePath"].Value.ToString());
// Throw on any error
transferResult.Check();
}
}
catch (Exception e) {
Dts.Variables["strExceptions"].Value = e.Message + ":" + e.StackTrace;
Dts.TaskResult = (int)ScriptResults.Failure;
} Dts.TaskResult = (int)ScriptResults.Success;
}
The variables used in the above code is self explanatory.... Remember to get the thumbprint using WinSCP initially to get the certificate or check with the FTP system admin.

Comments

Post a Comment

Popular posts from this blog

Load Data into Azure DW using C# in an SSIS script task

Image
Now there are a lot of reasons why SSIS needs to be leveraged for loading data into the Azure DW platform. Even though Polybase and Azure Data factory are the core criteria's, here are the templates for the SSIS script task that were leveraged to load data (full and incremental into Azure DW) for a specific customer rather than using the data flow task: SSIS Full Load script : SSIS Incremental Load script : There are a few reasons for this approach and one of them being that the existing package was using a similar structure and one not to be deviated from. The other being that some key logging aspects needed to be handled in a Legacy platform that could not be decommissioned at that time.
Read more

Branding your SharePoint site in a super fast way - Emgage

Branding a SharePoint farm on premise or in O365 is an extremely time absorbing task. Recently found a few tools called emgage prime and emgage turbo which basically gives you customized UX branding solutions to make you get to what you want super fast and super easy....Just used it on a SP2016 farm a few days ago and the results were tremendous. A definite recommendation on the SP side of things.
Read more

Power BI To Embed Or Not To Embed

It is very critical for organizations to work & play with data. Power BI - the reporting solution from Microsoft is literally scorching the market with its rapid pace in usage. On a quick note while interacting with your Power BI report like the following--> This report is accessible by the public. In order to create a more personalized/advanced security reporting structure with Power BI, the Power BI embedded would be the way to go. Create a workspace collection in Azure and then generate the required API keys (two by default - primary and secondary). These API keys will be leveraged by your web application. Once this is done create the pbix solution file in your desktop tool and publish or import the pbix solution to the Azure workspace using powershell/C#/ruby/java etc... Now to interact with the pbix file in your application, you need to leverage the Power BI Embed API's. However there is another approach using Power BI API's instead of the embed API's. The em
Read more