SSIS 2008 - flat file source look up other flat file with foreach loop

I don't know what this title should be but here's simplified objectives


1. Pull from a specified directory all files matching a format of “007C0*.log
2. From each error log, you must retrieve the line number from the contents of the error log and the error message
3. Using the file name, pull from  another directory the file that has the same base name, but with a “txt” extension
4. Using the Line Number from the error log, pull the line data from the text file.
5. Use both the information from the error log and the upload file to populate the table

log file format (007C0*.log)

text file format (logfilename_datetimestamp.txt)

Objective 1 and 2 can be done in one foreach loop.
For objective 3, I needed one more foreach loop since text file consists of full log file name and datetimestamp.
For objective 4, I had to put a data into somewhere but I was asked not to put in our database so I had to use cache transform. That means, I had to create two packages - parent and child since cache transform cannot be placed in a for loop(connection problem).

So lets start!

1) create five variables.

2) put a foreach loop container and double click to open up an editor.

click Collection and click + sign to expand Expressions and click ... to put new values

3) Pick Directory and FileSpec  from Property column drop down menu.

and Click ... to add expression.

(In this example, FileSpec is a filename and  Directory is the logfilesource.)

4) click Variable Mappings tab and pick [User::LogFileName] and close an editor.

5) add a Script Task inside Foreach Loop Container. (need to get a log file name without a path and file extension)

Click ReadWriteVariables row and add [User::LogFileName] and click Edit Script

5.1) simple code

         public void Main()

        {   

            String str = Dts.Variables["User::LogFileName"].Value.ToString();            

            String logfilename = str.Substring(3, str.IndexOf(".")-3);

            Dts.Variables["User::LogFileName"].Value = logfilename;            

            Dts.TaskResult = (int)ScriptResults.Success;

        }

* the reason why I used "3"s in str.substring is that the path is "C:\". If your path(directory) is like "C:\Users\asung\Documents\Visual Studio 2008\Projects\Integration Services Project3" then you need to use a different number. 

6) add a new Foreach Loop Container inside and set it up like the one before

But this time FileSpec Expression should be @[User::LogFileName] + "*.txt"

7) click Variable Mappings tab and pick [User::TxtFileName] and close an editor.

8) add a Script Task inside an inner Foreach Loop Container and repeat step 5 with [User::TxtFileName]

8.1) just like 5.1

        public void Main()

        {

            String str = Dts.Variables["User::TxtFileName"].Value.ToString();

            Dts.Variables["User::TxtFileName"].Value = str.Substring(11);

            Dts.TaskResult = (int)ScriptResults.Success;

        }

9) add an Execute Package Task

Next is to create a child package and pass variables with this Execute Package Task.