Download most recent file from FTP using SSIS

Problem: We need to download the most recent file in past 7 days from FTP.

Solution: This can be done in various different ways, in this article we will be using script task to accomplish this goal.

Our Control Flow tab would look like this

 

 

First we will create variables that we would need.

Filename: For getting the most recent filename

ftpPath: Location where FTP points (you can get this location by using Filezilla, use the credentials to see the root directory and browse to directory containing the files)

 

Configure Script Task:

Script:

 

/*

Microsoft SQL Server Integration Services Script Task

Write scripts using Microsoft Visual C# 2008.

The ScriptMain is the entry point class of the script.

*/

 

using System;

using System.Collections;

using System.Data;

using System.Net;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Text;

using System.Collections.Generic;

 

namespace ST_cd51cf97b767493b83089c7b08c3e8d2.csproj

{

[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

{

 

#region VSTA generated code

enum ScriptResults

{

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

};

#endregion

 

/*

        The execution engine calls this method when the task executes.

        To access the object model, use the Dts property. Connections, variables, events,

        and logging features are available as members of the Dts property as shown in the following examples.

 

        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;

        To post a log entry, call Dts.Log("This is my log text", 999, null);

        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

 

        To use the connections collection use something like the following:

        ConnectionManager cm = Dts.Connections.Add("OLEDB");

        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

 

        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

        

        To open Help, press F1.

    */

 

public void Main()

{

try

{

// Below is just a dummy ftp address. Please use you true ftp address and credentials

 

FtpWebRequest myftpWeb = (FtpWebRequest)WebRequest.Create("ftp://127.0.0.1/");

myftpWeb.Credentials = new NetworkCredential("xyz", "xyz");

List<MyFileProperties> arr = new List<MyFileProperties>();

myftpWeb.Method = WebRequestMethods.Ftp.ListDirectoryDetails;

WebResponse webR = myftpWeb.GetResponse();

 

StreamReader reader = new StreamReader(myftpWeb.GetResponse().GetResponseStream());

String readline;

String[] dataInString;

while (!reader.EndOfStream)

{

readline = reader.ReadLine();

if (readline.Trim().EndsWith(".csv"))

{

dataInString = readline.Split(" ".ToCharArray());

DateTime dd = DateTime.Parse(DateTime.Now.Year + dataInString[16] + dataInString[17]);

Console.WriteLine(Math.Floor((DateTime.Now - dd).TotalDays));

int DaysCount = (int)Math.Floor((DateTime.Now - dd).TotalDays);

if (DaysCount <= 8)

{

 

 

arr.Add(new MyFileProperties()

{

Name = dataInString[19],

Days = DaysCount

});

}

}

}

arr.Sort();

 

string str = arr[0].Name;

 

reader.Close();

reader = null;

myftpWeb = null;

 

Dts.Variables["filename"].Value = str;

 

Dts.TaskResult = (int)ScriptResults.Success;

}

catch (Exception ex)

{

Dts.Events.FireError(0, "getMostRecentFile", "Error: " + ex.Message, string.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}

}

}

public class MyFileProperties:IComparable

{

public string Name { get; set; }

public int Days { get; set; }

 

#region IComparable Members

 

public int CompareTo(object obj)

{

if (obj is MyFileProperties)

{

MyFileProperties myfile = (MyFileProperties)obj;

return Days.CompareTo(myfile.Days);

}

else

throw new ArgumentException("Object is not MyFileProperties");

}

 

#endregion

}

 

}

 

FTP Task Configuration

Create an FTP Connection Manager

File Transfer Configuration

 

FTP Task Editor

 

Result

Tags: , , , ,

SSIS