Hi Guys, Need to receive 3 files from FTP Location. How can i achieve this with script task in ssis. Thanks, Mahek

Here is a link for you to an article that contains the code: http://www.networksteve.com/enterprise/topic.php/SSIS_Script_task_that_FTP's_files/?TopicId=16873&Posts=1Arthur My Blog

Hi Arthur, Link which you provide is for send files to FTP... I tried by change it to receive,,,,but it doesnt Work...Below is my code.... Dim files() As String files(1) = "/NetworkActivity_6096_2590334_09-06-2010.log.gz" files(2) = "/NetworkClick_6096_09-06-2010.log.gz" files(3) = "/NetworkImpression_6096_09-06-2010.log.gz" ftp.ReceiveFiles(files, "O:\Test1\", True, True) Need to receive this three files from FTP to O:\Test1\.... Thanks, Mahek

why you want to use scripting for this? you can do it simply with FTP Task , look at this sample: http://www.rad.pasfu.com/index.php?/archives/28-Transfer-Multiple-Files-from-or-to-FTP-remote-path-to-local-path-SSIS.htmlhttp://www.rad.pasfu.com

Hi Reza, Thanksfor the reply... I have other files also on the FTP .... I dont want all files .... i just want to develop pkg that load these three files.. Also i need to configure pkg that ,,, if some one enter the date , at that moment that person can receive these three files for the date he entered...

OK, fair enough, so you can write script for this in script task or put file names in a database table or csv file or another sources, then loop through files with foreach loop and transfer them with ftp.http://www.rad.pasfu.com

Hi Mahek, if your file names will not change...keep them in database table...and loop over that table with ForEach loop each time getting the name of the file in to a variable. and if we have the name of the file in a variable we can receive only that file from the FTP location using FTP Task. I tried this. so with three values in the table you are able to receive only three files. settings will be like follow set IsRemotePathVariable --> True set IsLocalPathVariable --> True set RemoteVariable --> to the name of the file that you want to receive from FTP Location set LocalVariable --> to the Directory Path where you want to store the received file. you can try this even if your file name changes, provided you can find the name of the required file logically (i.e in program.) provide further details if you feel that I did not understand your problem..

I had wrote below listed script for it, but files are not receiving in local directory and pkg finished successfully... ftp.Connect() Dim files() As String files(1) = "/NetworkActivity_6096_2590334_09-06-2010.log.gz" files(2) = "/NetworkClick_6096_09-06-2010.log.gz" files(3) = "/NetworkImpression_6096_09-06-2010.log.gz" ftp.ReceiveFiles(files, "O:\Test1\", True, False) ftp.close() Please tell me whats wrong with my code ..... I got success to receive all the files by using below listed code... Dim fileNames() As String Dim folderNames() As String ftp.GetListing(folderNames, fileNames) ftp.ReceiveFiles(fileNames, "o:\Test1\", True, True) Thanks,,

Hi, Thanks for your reply. I understand your point. But in my case rek is : I need to configure pkg like ...when someone enter the date ,,then he will get three different files for only that date... Date is appended in the end of file name on FTP... /NetworkActivity_6096_09-06-2010.log.gz"

this sample C# code will download single file from FTP: // Get the object used to communicate with the server. FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://www.contoso.com/test.htm"); request.Method = WebRequestMethods.Ftp.DownloadFile; // This example assumes the FTP site uses anonymous logon. request.Credentials = new NetworkCredential ("anonymous","janeDoe@contoso.com"); FtpWebResponse response = (FtpWebResponse)request.GetResponse(); Stream responseStream = response.GetResponseStream(); StreamReader reader = new StreamReader(responseStream); Console.WriteLine(reader.ReadToEnd()); Console.WriteLine("Download Complete, status {0}", response.StatusDescription); reader.Close(); response.Close(); you can put your file full ftp path instead of "ftp://www.contoso.com/test.htm" reference: http://msdn.microsoft.com/en-us/library/ms229711.aspxhttp://www.rad.pasfu.com

Hi Reza, Can you please code them in VB.net as i am using visual studio for this task. I am not that much familiar with C# code.

here the code in VB.NET Dim request As FtpWebRequest = WebRequest.Create("ftp://www.contoso.com/test.htm") request.Method = WebRequestMethods.Ftp.DownloadFile ' This example assumes the FTP site uses anonymous logon. request.Credentials = New NetworkCredential("anonymous", "janeDoe@contoso.com") Dim response As FtpWebResponse = request.GetResponse() Dim responseStream As Stream = response.GetResponseStream() Dim reader As StreamReader = New StreamReader(responseStream) Console.WriteLine(reader.ReadToEnd()) Console.WriteLine("Download Complete, status {0}", response.StatusDescription) reader.Close() response.Close() Note that you should Import System.Net and System.IOhttp://www.rad.pasfu.com

Hi Reza, i need to check the file is exist in directory on ftp or not...I am using below listed code....its not working..... Dim strFolders As String() Dim strFiles As String() Dim fileName As String ftp.SetWorkingDirectory("Matchtables") ftp.GetListing(strFolders, strFiles) For Each fileName In strFiles If (fileName = "matchtables_6096_09-06-2010.done") Then ------- end If Next I had done research on this , i get only this code to set any directory on FTP as working directory.... Please check and let me know whats wrong with my code.... Thanks, Mahek

I think the filename contains Full path of each file, you can check it with a messagebox.Show(filename) http://www.rad.pasfu.com

Actually i had got success with the same code for the files which are directly in the FTP , Not in any directory,,,, But its not working for Particular directory only... Is my code is correct to check for the file in particular directory??

the method you used for retrieving list of files with GetListing is obselete, use this function to fetch list of files in remote ftp address: Public Function GetFileList() As String() Dim downloadFiles As String() Dim result As StringBuilder = New StringBuilder() Dim reqFTP As FtpWebRequest Try reqFTP = FtpWebRequest.Create(New Uri("ftp://" + ftpServerIP + "/")) reqFTP.UseBinary = True reqFTP.Credentials = New NetworkCredential(ftpUserID, ftpPassword) reqFTP.Method = WebRequestMethods.Ftp.ListDirectory Dim response As WebResponse = reqFTP.GetResponse() Dim reader As StreamReader = New StreamReader(response.GetResponseStream()) Dim line As String = reader.ReadLine() While (line) result.Append(line) result.Append("\n") line = reader.ReadLine() End While ' to remove the trailing '\n' result.Remove(result.ToString().LastIndexOf("\n"), 1) reader.Close() response.Close() Return result.ToString().Split("\n") Catch ex As Exception System.Windows.Forms.MessageBox.Show(ex.Message) downloadFiles = Nothing Return downloadFiles End Try End Function http://www.rad.pasfu.com

Hi Reza, Do you know how we can check particular three files are available in Local directory using SQLcode or script code?? I know if its for one file we can check it by IF File.Exists (fullfilepath)....then....EndIF How we can achieve the same thing for three diff files??? Thanks, Mahek