SSIS - Foreach Loop RRS feed

  • Question

  • Hi,

    I want to select *.txt and *.xls files from a particular folder thru foreach loop in SSIS. How can I specify both the file extension in foreach loop.

    please suggest.

    Friday, July 6, 2012 8:17 AM


All replies

  • 1. you can have seperate foreach loop containers to loop through *.txt files and another for each loop container for *.xls files.

    2. using a single foreach loop container, edit the container and select collection on the top left property menu and on "Files" type in *.* so to look for all files than have a variable that will store file name for every loop. then before you do anything with a looped file (i.e. move file using a file system task) insert a script task and join it to a file system task on condition that mapped variable "file name" ends with *.txt or *.xls.

    let me know if this makes sense, I can give you a screenshot if necessary.

    BI Technical Consultant| Karabina, South Africa

    Friday, July 6, 2012 8:42 AM
  • Hi,

    You can do it by putting the Script task first in the for loop and then check for the ext there as shown below

     Dim filename As String
            Dim ext As String
            filename = Dts.Variables("User::FileName").Value.ToString()
            ext = filename.Substring(filename.Length - 3)
            If ext.ToUpper() = "TXT" Or ext.ToUpper() = "XLS" Then
                Dts.Variables("User::isExtentionValid").Value = True
                Dts.Variables("User::isExtentionValid").Value = False
            End If

    Now, next set precedence constraint  to "Expression and Constraint" and set below expression

    @[User::isExtentionValid] == True
    Just make sure to create variable isExtentionValid of bool type.

    - Chintak (My Blog)

    Friday, July 6, 2012 8:47 AM
  • As far as I know you can use only * as a wildcard. The documentation isn't really specific on this point, so it's a shot in the dark.

    You could try *.*x*, which will take *.xls (I  think * means zero or more characters) and *.txt, but also any other extension with an x in it's name.

    If this doesn't work, I would create two for each loops, one for .xls and one for .txt.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    Friday, July 6, 2012 8:49 AM
  • You could use my regex foreach loop with a regular expression like: ^.*\.(xls|txt)$

    or use a piece of script: http://microsoft-ssis.blogspot.nl/2012/04/regex-filter-for-foreach-loop.html

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Friday, July 6, 2012 9:47 AM
  • nice script there SSISJoost

    BI Technical Consultant| Karabina, South Africa

    Friday, July 6, 2012 9:56 AM