SSIS Foreach loop container dynamic file name and path , then unzip files

Question

I have a folder having multiple files with the name as

P04_20140326_1234.zip
P04_20130324_58714.zip
P04_20130808_jurhet.zip
P04_20130815_85893.zip
etc

The name is in the format P04_systemdate_*.zip.

I want to pick the folder containing currentdate in the name and unzip it first and load the data from extracted file into the table.eg : file named as A.txt goes into table A, filenamed as B goes into table B and so on...

Answer 1

I guess you have already done the following:

1. Add a Data Flow
2. Inside the data flow, add a flat file source, and Ole_DB destination
3. Configure the flat file source to point to one of your files and connect all the appropriate columns so that data flows from file to database.

If all of this is already working, then let's do the For-Each loop

1. Create a variable (default to package root level) and call it CsvFileName of type string
2. Add a ForEach loop (not a For loop)
3. Change loop type to be a Foreach File Enumerator
4. Set your folder path and look for *.csv
5. Under Variable mappings, add the variable "User::CsvFileName" variable, and set the index to 0 - this means that all file names returned from the Foreach loop will show up in the variable.
6. In the Connection Managers (bottom) right click on the FlatFileSource, and choose properties
7. Set the DelayValidation to "True"
8. Click on Expressions, and then click on the ellipsis
9. Set the ConnectionString property to use the "CsvFileName" variable

Run it. This should load all files. Now, if you just want to restrict it to a date here's what you do:

1. Create a variable called "FilterDate"
2. Set the value to whichever date you want to set (20140322, for example)
3. In the ForEach loop, go to Collections, and then click on Expressions, then click the ellipsis
4. Set the FileSpec property to be "*" + @[User::FilterDate] + "*.csv"

Now it will only filter the files that you want.