How To Add Multiple File Extensions To The Files: Input Field In The Foreach Loop Container Ssis

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 n

Solution 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

   PublicSub Main()
   
       Dim strFile AsString = Dts.Variables.Item("Filename").Value.ToString
   
       SelectCase IO.Path.GetExtension(strFile).ToLower
           Case".csv", ".zip"
               Dts.Variables.Item("Flag").Value = TrueCaseElse
               Dts.Variables.Item("Flag").Value = FalseEndSelect
   
   
       Dts.TaskResult = ScriptResults.Success
   EndSub

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

Solution 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