CODEWITHSUNDEEP
ssissftpwinscp
Neeraj Bhanot
Neeraj Bhanot

Reputation: 57

Running Winscp from SSIS execute process task

I am trying to implement solution in SSIS in which we have to download files from SFTP using Winzip. Filenames in SFTP are like ABC_CNT_03_06_2019_02-05_AM.csv. The requirement is to download the current date file. In this case we have to ignore the time part in the file name.

Currently, I am trying to implement the below solution:

  1. Created a variable (User::Filename) in ssis to create the file name ABC_CNT_03_06_2018*.csv
  2. In Execute process task : have selected C:\Program Files (x86)\WinSCP\WinSCP.com in executable
  3. Arguments : /script="F:\Upload.txt /parameter " + User::Filename
  4. upload.txt ha the below text option batch on option confirm off open username: [email protected] get /%1% F:\downloadfolder close exit

Trying to run the package but it is not working. Can you please suggest further?

Upvotes: 1

Views: 1994

Answers (1)

userfl89
userfl89

Reputation: 4790

For something like this I’d recommend using a Script Task with the WinSCP .NET assembly, which will allow you to use C# functionality to verify the date on the file prior to the transfer. An example of this process is below. Since you’re storing the beginning of the file name (including date) in a variable, the String.StartsWith method is used on the Name property, as this only returns the file name without the folder path. The String.EndsWith method is also used to match only CSV files as mentioned in your post. The SSIS variable holding the file name will need to be added in the ReadOnlyVariables field of the Script Task, as well as the variables holding folder paths if that’s how you’re storing these. If you encounter an error relating to loading the WinSCP assembly,the ResolveEventHandler delegate can be used to resolve this as done below. The location where the WinSCP dll was downloaded will be the path sent to the LoadFile method. While it looks like you are only expecting one file per day, a list is used in the event that there will be multiple files on a single day.

using System.Linq;
using WinSCP;
using System.Collections.Generic;

//load WinSCPnet.dll  
 static ScriptMain()
 {
     AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(CurrentDomain_AssemblyResolve);
 }
 static System.Reflection.Assembly CurrentDomain_AssemblyResolve(object sender, ResolveEventArgs args)
 {
     if (args.Name.ToUpper().Contains("WINSCPNET"))
     {
         string path = @"C:\WinSCP Download Path\";
         return System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(path, "WinSCPnet.dll"));
     }
     return null;  
 }

    public void Main()
    {
      //get file name and local/remote paths from SSIS variables.
      string fileDateName = Dts.Variables[“User::FileDateName”].Value.ToString();
      string remotePath = Dts.Variables[“User::RemoteFilePath”].Value.ToString();    
      string localPath = Dts.Variables[“User::LocalFilePath”].Value.ToString();

 SessionOptions sessOpt = new SessionOptions
   {
     Protocol = Protocol.Sftp,
     HostName = "SFTPsite.com",
     UserName = "user",
     Password = "password",
     SshHostKeyFingerprint = "Your SshHostKeyFingerprint"
   };

 using (Session session = new Session())
 {
   session.Open(sessOpt);

   TransferOptions transferOptions = new TransferOptions();
   transferOptions.TransferMode = TransferMode.Binary;


   RemoteDirectoryInfo rdi = session.ListDirectory(remotePath);

   //confirm beginning of file name and extension
   List<string> fileList = rdi.Files.Where(file => (file.Name.StartsWith(fileDateName))
   && (file.FullName.EndsWith(".csv"))).Select(file => file.FullName).ToList();

   foreach (string s in fileList)
   {
       //transfer files
       session.GetFiles(s, localPath, false, transferOptions);
   }

 }

}

Upvotes: 1

Related Questions