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 :)).
https://winscp.net/eng/docs/library_example_recursive_download_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/Tips/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 System.Windows.Forms;
using WinSCP;

namespace ST_0211b76178e849199217ecc2d4ccaa07
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.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();

                    // Connect
                    session.Open(sessionOptions);

                    // Download files
                    TransferOptions TransferOptions = new TransferOptions();
                    TransferOptions.TransferMode = TransferMode.Binary;
                    //TransferOptions.FileMask = "*.zip";
                    TransferOperationResult transferResult =
                            //session.QueryReceived += (sender, e) =>
                            //{
                            //    Console.WriteLine("Error: {0}", e);
                            //    e.Continue();
                            //};
                    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)DTSE*xecResult.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)DTS*ExecResult.Failure;
            }
        }
    }
}
[/code]

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:
31,635
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
Joined:
Posts:
10
Location:
Rennes

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

martin
Site Admin
martin avatar
Joined:
Posts:
31,635
Location:
Prague, Czechia

anton35 wrote:

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.
Even recursively? Like COPY_folder\COPY_subfolder\COPY_subsubfolder?

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
31,635
Location:
Prague, Czechia

anton35 wrote:

Yes.
It's a simple tree, there are no subfolders.
So "NO", not "YES"?

Reply with quote

martin
Site Admin
martin avatar
Joined:
Posts:
31,635
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

martin
Site Admin
martin avatar
Joined:
Posts:
31,635
Location:
Prague, Czechia

Good. Sorry I've posted PowerShell code by mistake, forgetting that you use C#.

Reply with quote

Advertisement

You can post new topics in this forum