Move files dropped on an FTP to a local folder without deleting the FTP folders.

Advertisement

anton35
Joined:
Posts:
10
Location:
Rennes

Move files dropped on an FTP to a local folder without deleting the FTP folders.

Hello,

I would like to retrieve files deposited from the separate folders of a SFTP and then delete them but keeping the tree (different access rights depending on the users).

I used session.GetFiles with transferOptions.FileMask but I can not get what I want with it.
Depending on the settings either there is an error in the script or there is no error but nothing has been moved:
// Download files
TransferOptions TransferOptions = new TransferOptions();
TransferOptions.TransferMode = TransferMode.Binary;
TransferOptions.FileMask = "*.zip";
TransferOperationResult transferResult = session.GetFiles(
   "/*", @Dts.Variables["$Project::PROP_FilePath"].Value.ToString(), true, TransferOptions);
https://winscp.net/eng/docs/file_mask

I have also tried using session.QueryReceived to override errors (folders are protected and can not be deleted).
But I can not even integrate it into the script without a compilation error (I'm not a developer and I just started SSIS :)).
Recursively download directory tree with custom error handling

Can someone help me?
Thanks in advance.

PS: excuse my English, I'm French :)

Reply with quote

Advertisement

anton35
Joined:
Posts:
10
Location:
Rennes

Hello,
I did not start from these models:
https://winscp.net/eng/docs/library_ssis
https://winscp.net/eng/docs/library_session_getfiles#csharp
https://www.codeproject.com/Articles/677002/How-to-Use-SFTP-within-SSIS

I do not know where the logs are, I captured the execution result instead (attachment).
The complete copy of my code:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using WinSCP;
 
namespace ST_0211b76178e849199217ecc2d4ccaa07
{
    [SSISScriptTaskEntryPoint]
    public partial class ScriptMain : VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            SessionOptions sessionOptions = new SessionOptions
            {
                Protocol = Protocol.Ftp,
                FtpSecure = FtpSecure.Explicit,
                HostName = Dts.Variables["$Project::PROP_FTP_HostName"].Value.ToString(),
                UserName = Dts.Variables["$Project::PROP_FTP_UserName"].Value.ToString(),
                Password = Dts.Variables["$Project::PROP_FTP_Password"].Value.ToString(),
                TlsHostCertificateFingerprint = Dts.Variables["$Project::PROP_FTP_Fingerprint"].Value.ToString()
            };
 
            try
            {
                using (Session session = new Session())
                {
                    // As WinSCP .NET assembly has to be stored in GAC to be used with SSIS,
                    // you need to set path to WinSCP.exe explicitly, if using non-default location.
                    session.ExecutablePath = @Dts.Variables["$Project::PROP_CheminWinSCP"].Value.ToString();
 
                    //session.QueryReceived += (sender, e) =>
                    //{
                    //    Console.WriteLine("Error: {0}", e);
                    //    e.Continue();
                    //};
 
                    // Connect
                    session.Open(sessionOptions);
 
                    // Download files
                    TransferOptions transferOptions = new TransferOptions();
                    transferOptions.TransferMode = TransferMode.Binary;
                    //transferOptions.FileMask = "*.zip";
                    TransferOperationResult transferResult =
                    session.GetFiles(
                        "/*", @Dts.Variables["$Project::PROP_CheminFichiers"].Value.ToString(), true, transferOptions);
 
                    // Throw on any error
                    transferResult.Check();
 
                    // Print results
                    bool fireAgain = false;
                    foreach (TransferEventArgs transfer in transferResult.Transfers)
                    {
                        Dts.Events.FireInformation(0, null,
                            string.Format("Upload of {0} succeeded", transfer.FileName),
                            null, 0, ref fireAgain);
                    }
                }
 
                Dts.TaskResult = (int)DTSExecResult.Success;
            }
            catch (Exception e)
            {
                Dts.Events.FireError(0, null,
                    string.Format("Error when using WinSCP to upload files: {0}", e),
                    null, 0);
 
                Dts.TaskResult = (int)DTSExecResult.Failure;
            }
        }
    }
}

Description: the execution result instead

errors.png

Reply with quote

anton35
Joined:
Posts:
10
Location:
Rennes

Hello,
I tried this code, it works for upload but for the download it becomes complicated: reverse paths does not work (that's my goal).
Am I to understand that the 2 other tracks evoked will not succeed? (1: filemask / 2: error handling).
Thank you for your help.

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,453
Location:
Prague, Czechia

anton35 wrote:

I tried this code, it works for upload but for the download it becomes complicated: reverse paths does not work (that's my goal).
What are "reverse paths"?

Am I to understand that the 2 other tracks evoked will not succeed? (1: filemask / 2: error handling).
You cannot achieve what you want using file masks.

Reply with quote

anton35
Joined:
Posts:
10
Location:
Rennes

Hi,
This code lists local files and directories to upload on FTP and i want to do the opposite.
I tried to adapt the code but I can't :(
Ok for the filemask.
Other possible alternative: to force using error management is feasible?
Thanks a lot

Reply with quote

anton35
Joined:
Posts:
10
Location:
Rennes

my code that doesn't work :( :
using (Session session = new Session())
{
    // Connect
    session.Open(sessionOptions);
 
    // Enumerate files and directories to DOWNload
    IEnumerable<FileSystemInfo> fileInfos =
        new DirectoryInfo(remotePath).EnumerateFileSystemInfos(
            "*", SearchOption.AllDirectories);
 
    foreach (FileSystemInfo fileInfo in fileInfos)
    {
        string localFilePath =
            RemotePath.TranslateRemotePathToLocal(
                fileInfo.FullName, remotePath, localPath);
 
        if (fileInfo.Attributes.HasFlag(FileAttributes.Directory))
        {
            // Create remote subdirectory, if it does not exist yet
            if (!session.FileExists(localFilePath))
            {
                session.CreateDirectory(localFilePath);
            }
        }
        else
        {
            Console.WriteLine("Moving file {0}...", fileInfo.FullName);
            // DOWNload file and remove original
            session.GetFiles(fileInfo.FullName, localFilePath, true).Check();
        }
    }
}

Reply with quote

Advertisement

anton35

Hello, I would like to go a step further in the process in place by adding the same prefix to all downloaded directories (example: 'COPY _' + folderName)
=> is it possible? and how?
thanks in advance.

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,453
Location:
Prague, Czechia

Then it would be easier, to iterate the subfolders, like:
$subfolders =
    $session.EnumerateRemoteFiles(
        $remotePath, $Null, [WinSCP.EnumerationOptions]::EnumerateDirectories)
foreach ($subfolder in $subfolders)
{
    $dest = (Join-Path $localPath ("COPY_" + $subfolder.Name) "*")
    $session.GetFiles($subfolder.FullName + "/*", $dest).Check()
}
(untested)

Reply with quote

anton35
Joined:
Posts:
10
Location:
Rennes

Thanks,
I was able to make the change i wanted, everything works fine :
foreach (RemoteFileInfo fileInfo in fileInfos)
{
     string destFileName = fileInfo.FullName.Insert(1, "COPY_");
     string localFilePath =
            RemotePath.TranslateRemotePathToLocal(
                  destFileName, remotePath, localPath);
...
}
It helped me a lot.
Best regards.

Reply with quote

Advertisement

Advertisement

You can post new topics in this forum