locked
FTP File Properties RRS feed

  • Question

  • I'm using a script task. How do you connect the FTP site to loop through the files to get the most current file by the LastModifiedDate?
    Sunday, January 27, 2019 2:43 PM

Answers

  • Hi Michael,

    In SSIS Script Task you need to do 2 things:

    • Add WinSCP assembly in the project References from the GAC.
    • Add two namespaces.

    Please see below.


    Sunday, February 3, 2019 11:36 PM

All replies

  • https://www.wiseowl.co.uk/blog/s435/ssis-file-attributes.htm

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 27, 2019 3:00 PM
  • I know how to loop through files. How do you connect to the FTP site? Do you set the directory to the full FTP url or just after the domain? Thank you.
    Sunday, January 27, 2019 3:23 PM
  • Hi Michael,

    You need to unzip its 2 files: WinSCP.exe and WinSCPnet.dll and register the assembly in the GAC: https://winscp.net/eng/docs/library_install#installing

    Here is a sample for you how to use it.
    I am getting list of files from the "ftp.symantec.com".

    void Main()
    {
    	const string FTPSERVER = "ftp.symantec.com";
    	const string remoteDirectory = "/public/english_us_canada/antivirus_definitions/norton_antivirus/";
    	const string sessionLogPath = @"e:\temp\WinSCP.log";
    	const string executablePath = @"e:\Kit\WinSCP\WinSCP.exe";
    
    	try
    	{
    		// Setup session options
    		SessionOptions sessionOptions = new SessionOptions
    		{
    			Protocol = Protocol.Ftp,
    			HostName = FTPSERVER,
    			UserName = "anonymous",
    			Password = "password",
    		};
    
    		using (Session session = new Session())
    		{
    			session.ExecutablePath = executablePath;
    			session.SessionLogPath = sessionLogPath;
    			
    			// Connect
    			session.Open(sessionOptions);
    
    			// Get list of files and sub-directories in the directory
    			string remotePath = remoteDirectory;
    			RemoteDirectoryInfo directoryInfo = session.ListDirectory(remotePath);
    
    			foreach (RemoteFileInfo fileInfo in directoryInfo.Files
    				.Where(x => x.IsDirectory==false)
    				.OrderByDescending(f => f.LastWriteTime))
    			{
    				Console.WriteLine("{0}, {1}, {2}", fileInfo.Name, fileInfo.LastWriteTime, fileInfo.Length);
    			}
    		}
    		
    		Dts.TaskResult = (int)ScriptResults.Success;
    	}
    	catch (Exception ex)
    	{
    		Dts.Events.FireError(18, Dts.Variables["System::TaskName"].Value.ToString()
    		  , ex.Message.ToString()
    		  , "", 0);
    		Dts.TaskResult = (int)ScriptResults.Failure;
    	}
    }

    Output:
    md5-hash.txt, 1/27/2019 4:35:00 PM, 41434
    20190126-001-core15unix64.sh, 1/26/2019 6:03:00 PM, 475241593
    20190126-001-core15v5i32.exe, 1/26/2019 6:03:00 PM, 215136432
    20190126-001-core15v5i64.exe, 1/26/2019 6:03:00 PM, 216701736
    ...
    20190122-021-core31sdsv5i64.exe, 1/23/2019 12:56:00 PM, 70241128
    20190122-021-core31sdsv5n64.exe, 1/23/2019 12:56:00 PM, 71456592
    rsync.flg, 12/10/2014 12:00:00 AM, 0






    Sunday, January 27, 2019 5:38 PM
  • Hi Michael,

    Any update for this thread?

    Were you able to solve the problem?

    Please let us know.  


    Tuesday, January 29, 2019 11:08 PM
  • I'm getting this error when I rebuild the script task. I removed and re-installed it but it's the same error.

    This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is ..\packages\WinSCP.5.13.7\build\WinSCP.targets.

    I tried to restore the package using the package manager console. I also tried:

    1. Select the Tools > NuGet Package Manager > Package Manager Settings menu command.
    2. Set both options under Package Restore.
    3. Select OK.
    4. Build your project again.

    Thursday, January 31, 2019 1:32 AM
  • Hi Michael,

    No need for NuGet. It just complicates the environment.

    As I mentioned earlier, you need to do the following 2 steps:

    That's it.


    Thursday, January 31, 2019 4:19 AM
  • command prompt:

    C:\Users\tamle>gacutil -i C:\Users\michaelyarbrough\Desktop\notes\downloads my\WinSCP-5.13.7-Automation\WinSCP-5.13.7-Automation\WinSCPnet.dll
    'gacutil' is not recognized as an internal or external command,
    operable program or batch file.
    Sunday, February 3, 2019 5:25 PM
  • Hi Michael,

    Here is how to register .Net assembly in the GAC the easy way.

    Please download GACView uitility:

    http://www.nirsoft.net/dot_net_tools/gac_viewer.html

    • Launch it 'As Administrator'
    • Select in its menu File/Install Assembly
    • Select C:\Users\michaelyarbrough\Desktop\notes\downloads my\WinSCP-5.13.7-Automation\WinSCP-5.13.7-Automation\WinSCPnet.dll.

    It will register it in the GAC.


    Sunday, February 3, 2019 8:38 PM
  • I was able to register it using the GACView utility but I can't navigate to it to reference it in my SSIS script task.

    Windows can't find 'C:\Windows\assembly\GAC_MSIL'. Check the spelling and try again.
    Sunday, February 3, 2019 11:10 PM
  • Hi Michael,

    In SSIS Script Task you need to do 2 things:

    • Add WinSCP assembly in the project References from the GAC.
    • Add two namespaces.

    Please see below.


    Sunday, February 3, 2019 11:36 PM
  • Thank you!
    Monday, February 4, 2019 4:38 AM