How to add multiple file extensions to the Files: input field in the Foreach loop container SSIS

Question

How could I add multiple file extensions to the Files: input field in the Foreach loop container in SSIS 2008.

I have currently entered as *.zip OR .csv see image... but it doesn not work only works if I have one value like *.zip

Answer 1

I don't think you can specify two file type extensions in a For each Loop container , you should use *.* and you can filter for a specific extensions by using the following steps:

1. add a Variable of type boolean (ex: User::Flag)
2. add a script task inside the container and mark Filename variable as Readonly , Flag variable as ReadWrite

3. Inside the script write the following script

   Public Sub Main()
   
       Dim strFile As String = Dts.Variables.Item("Filename").Value.ToString
   
       Select Case IO.Path.GetExtension(strFile).ToLower
           Case ".csv", ".zip"
               Dts.Variables.Item("Flag").Value = True
   
   
           Case Else
               Dts.Variables.Item("Flag").Value = False
       End Select
   
   
       Dts.TaskResult = ScriptResults.Success
   End Sub
   

4. Add an expression to the Script task output connector like shown below

Or you can achieve it by using a Third-party components like Foreach File Enumerator with regex

Answer 2

Step 1) Create a variable which store the file name with the extension.
Step 2) Under the foreach loop container Map the variable which we created in step 1.
Step 3) create a execute sql task inside the foreach loop container, add connection and under sql statement enter the code insert into tablename(filename) values (?).
Step 4) Under parameter mapping select the variable which we created under variable name, changed the data type to nvarchar and changed parameter to 0.
Step 5) With the help of string function then you can create an expression with the help of string function like right