FTP using SSIS - How to determine File Time Stamp on remote server ?

Question

Post reply

FTP using SSIS - How to determine File Time Stamp on remote server ?

rob cushen

Old Hand

Points: 319
More actions
January 18, 2011 at 8:41 am

#144430

Hi,

Using SQL2005 SSIS...

I would like to get the file time stamp of the file before I download it.

It appears that the GetListing method only provides directory and filename.

Any other way to get it within a Script task ?

Thanks !

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 1

rob cushen (1/18/2011)

Hi,

Using SQL2005 SSIS...

I would like to get the file time stamp of the file before I download it.

It appears that the GetListing method only provides directory and filename.

Any other way to get it within a Script task ?

Thanks !

The link below should help you out:

http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/b037ba66-e105-4bf4-b070-680b1a0f5fcb/

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

rob cushen Old Hand Points: 319 More actions · Answer 2

Thanks for the response !

I was kind of hoping to accomplish it somehow via the FtpClientConnection Class within vb.net (within a Script Task in SSIS).

Within the FtpClientConnection Class I do not see a Method available in order to do what should be a simple task (obtain the time stamp of the file). I only want to download the file if it has a "new" timestamp.

I am hoping that I am overlooking some obvious answer and that someone can set me straight.

I will even settle for a "Rename" method within the FtpClientConnection Class.

Any ideas ?

matthew.mcgee Valued Member Points: 73 More actions · Answer 3

I wrote this little gem that works from within an SSIS Script task.

It puts the filenames and timestamps in a dataset which you can put in a variable,.....

Enjoy

/// <summary>

/// get the filelist with lastmodified time stamps from the specified ftp site

/// </summary>

/// <param name="FTP_HostName">the ftp site without the ftp:// prefix</param>

/// <param name="FTP_Password"></param>

/// <param name="FTP_UserName"></param>

/// <returns></returns>

public static System.Data.DataSet GetFilesWithDates(string FTP_HostName, string FTP_Password, string FTP_UserName)

{

string[] delimiters = { "\t" };

string[] fileinfo = { "", "" };

WebRequest FNrequest = FtpWebRequest.Create(string.Format("ftp://{0}:{1}@{2}/", FTP_UserName, FTP_Password, FTP_HostName));

FNrequest.Proxy = null;

FNrequest.Method = WebRequestMethods.Ftp.ListDirectory;

DataTable dtFiles = new DataTable(); dtFiles.Columns.Add("filename", typeof(string)); dtFiles.Columns.Add("date", typeof(string));

string FNLine; string FDLine;

Dictionary<string, string> filenames = new Dictionary<string, string>();

using (FtpWebResponse FNameResponse = (FtpWebResponse)FNrequest.GetResponse())

using (TextReader Reader = new StreamReader(FNameResponse.GetResponseStream()))

do

{

FNLine = Reader.ReadLine();

if (FNLine != null)

{

WebRequest FDRequest = FtpWebRequest.Create(string.Format("ftp://{0}:{1}@{2}/{3}", FTP_UserName, FTP_Password, FTP_HostName, FNLine));

FDRequest.Proxy = null;

FDRequest.Method = WebRequestMethods.Ftp.GetDateTimestamp;

using (FtpWebResponse FDateResponse = (FtpWebResponse)FDRequest.GetResponse())

FDLine = FDateResponse.LastModified.ToUniversalTime().ToString();

fileinfo[0] = FNLine;

fileinfo[1] = FDLine;

dtFiles.Rows.Add(fileinfo);

FDRequest = null;

}

} while (FNLine != null);

DataSet ds = new System.Data.DataSet();

ds.Tables.Add(dtFiles);

return ds;

}