locked
SSIS - Foreach Loop RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
        Else
            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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    nice script there SSISJoost

    BI Technical Consultant| Karabina, South Africa

    Friday, July 6, 2012 9:56 AM