none
For Each Loop Container to Loop through files in FTP site

    Question

  • Is there any way by which I can loop through the files in FTP site  using For Each Loop Container ? I have multiple files in FTP site which I need to load into my SQL database.

    Any suggestion would be highly appreciated.

    Thanks,

    Monday, August 23, 2010 6:16 PM

Answers

  • Use Script component to FTP the files. Its better than using FTP task bcoz the FTP task gives problem with the Password when moving from one environment to other. And you cannot configure it in better way.

    These links wiill be helpful to you.

    http://www.mssqltips.com/tip.asp?tip=1641

    http://www.sqldev.org/sql-server-integration-services/ssis-script-task-that-ftps-files-3178.shtml

    The advantage is you can configure the FTP ServerName, Port, UserName, PWD etc easily through SSIS Variables. Also, using script component you can get the list of files in FTP folder and out of these files if you want only specific files, you can do the same through SCRIPT Component.

    I derive this conclusion based on teh research i did recently and also impemented the same.

    Hope you find this helpful

     

    SAMPLE CODE:

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic
    ' The ScriptMain class is the entry point of the Script Task.
    
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports Microsoft.SqlServer.Dts.Runtime.FtpClientConnection
    Imports System.Text  ' need to add this one
    
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
     Enum ScriptResults
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
     End Enum
    
     Public Sub Main()
    
      ' STEP 1
    
      Dim ftpFileNameListXML As New StringBuilder
      ftpFileNameListXML.AppendLine("<filelist>")
    
      ' STEP 2
    
      Dim ftpcm As ConnectionManager
      ftpcm = Dts.Connections("FTP Connection Manager")
    
      Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpcm, Dts.Variables("FtpServer").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpcm, Dts.Variables("FtpPort").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpcm, Dts.Variables("FtpUser").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpcm, Dts.Variables("FtpPassword").Value)
    
      Dim ftp As FtpClientConnection = New FtpClientConnection(ftpcm.AcquireConnection(Nothing))
      Dim ftpFileNames() As String
      Dim ftpFolderNames() As String
    
      'ftpFileNames = Nothing
      'ftpFolderNames = Nothing
    
      ' STEP 3
      ftp.Connect()
      ftp.SetWorkingDirectory(Dts.Variables("FtpWorkingDirectory").Value.ToString())
      ftp.GetListing(ftpFolderNames, ftpFileNames)
      ftp.Close()
    
      ' STEP 4
      Dim i As Integer
      For i = 0 To ftpFileNames.GetUpperBound(0)
       ' add xml element
       ftpFileNameListXML.Append("<file name='")
       ftpFileNameListXML.Append(ftpFileNames(i))
       ftpFileNameListXML.AppendLine("'/>")
      Next i
      ' STEP 5
      ftpFileNameListXML.AppendLine("</filelist>")
      Dts.Variables("FtpFileListXML").Value = ftpFileNameListXML.ToString()
    
      Dts.TaskResult = ScriptResults.Success
     End Sub
    
    End Class
    

     

    Good Luck

     

     

     

     


    BR, AWM
    Monday, August 23, 2010 6:30 PM
  • I wonder if this might help -> http://beyondrelational.com/blogs/sudeep/archive/2010/08/18/control-flow-based-on-the-input-file-name.aspx


    This link was sent to me by Sudeep in reference to a problem I had but I recall it dealt with getting FTP files that met certain criteria (file length, name, mask etc..).  In response to looking for files "with certain characters" ... maybe Sudeep's blog will help.

    Monday, August 23, 2010 6:43 PM

All replies

  • Hello,

    You don't have to use foreach loop to get the files from FTP Site.

    Configure the FTP task and point to the Folder from which you want to download all the files.

    Then it will download all the files from the specific folder.

    --Edited: See the link for details

    http://www.developerdotstar.com/community/node/344

    see few lines at very end.

    Thanks

    Monday, August 23, 2010 6:20 PM
  • Thanks alot for the prompt reply!!!

    But I need to get the files with certain characters in it. For example : I need to get only those file which has today's datestamp on it.

    Monday, August 23, 2010 6:28 PM
  • Use Script component to FTP the files. Its better than using FTP task bcoz the FTP task gives problem with the Password when moving from one environment to other. And you cannot configure it in better way.

    These links wiill be helpful to you.

    http://www.mssqltips.com/tip.asp?tip=1641

    http://www.sqldev.org/sql-server-integration-services/ssis-script-task-that-ftps-files-3178.shtml

    The advantage is you can configure the FTP ServerName, Port, UserName, PWD etc easily through SSIS Variables. Also, using script component you can get the list of files in FTP folder and out of these files if you want only specific files, you can do the same through SCRIPT Component.

    I derive this conclusion based on teh research i did recently and also impemented the same.

    Hope you find this helpful

     

    SAMPLE CODE:

    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic
    ' The ScriptMain class is the entry point of the Script Task.
    
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime
    Imports Microsoft.SqlServer.Dts.Runtime.FtpClientConnection
    Imports System.Text  ' need to add this one
    
    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
     Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
     Enum ScriptResults
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
     End Enum
    
     Public Sub Main()
    
      ' STEP 1
    
      Dim ftpFileNameListXML As New StringBuilder
      ftpFileNameListXML.AppendLine("<filelist>")
    
      ' STEP 2
    
      Dim ftpcm As ConnectionManager
      ftpcm = Dts.Connections("FTP Connection Manager")
    
      Dts.Connections("FTP Connection Manager").Properties("ServerName").SetValue(ftpcm, Dts.Variables("FtpServer").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerPort").SetValue(ftpcm, Dts.Variables("FtpPort").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerUserName").SetValue(ftpcm, Dts.Variables("FtpUser").Value)
      Dts.Connections("FTP Connection Manager").Properties("ServerPassword").SetValue(ftpcm, Dts.Variables("FtpPassword").Value)
    
      Dim ftp As FtpClientConnection = New FtpClientConnection(ftpcm.AcquireConnection(Nothing))
      Dim ftpFileNames() As String
      Dim ftpFolderNames() As String
    
      'ftpFileNames = Nothing
      'ftpFolderNames = Nothing
    
      ' STEP 3
      ftp.Connect()
      ftp.SetWorkingDirectory(Dts.Variables("FtpWorkingDirectory").Value.ToString())
      ftp.GetListing(ftpFolderNames, ftpFileNames)
      ftp.Close()
    
      ' STEP 4
      Dim i As Integer
      For i = 0 To ftpFileNames.GetUpperBound(0)
       ' add xml element
       ftpFileNameListXML.Append("<file name='")
       ftpFileNameListXML.Append(ftpFileNames(i))
       ftpFileNameListXML.AppendLine("'/>")
      Next i
      ' STEP 5
      ftpFileNameListXML.AppendLine("</filelist>")
      Dts.Variables("FtpFileListXML").Value = ftpFileNameListXML.ToString()
    
      Dts.TaskResult = ScriptResults.Success
     End Sub
    
    End Class
    

     

    Good Luck

     

     

     

     


    BR, AWM
    Monday, August 23, 2010 6:30 PM
  • I wonder if this might help -> http://beyondrelational.com/blogs/sudeep/archive/2010/08/18/control-flow-based-on-the-input-file-name.aspx


    This link was sent to me by Sudeep in reference to a problem I had but I recall it dealt with getting FTP files that met certain criteria (file length, name, mask etc..).  In response to looking for files "with certain characters" ... maybe Sudeep's blog will help.

    Monday, August 23, 2010 6:43 PM
  • Here is an other example of a Foreach Loop FTP File enumerator:
    http://microsoft-ssis.blogspot.com/2011/08/foreach-ftp-file-enumerator.html

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Saturday, August 06, 2011 10:30 PM
    Moderator