Dynamic connection in SSIS using Expressions

Here is the Control Flow: Scenario:
We have a set of text files that contains information about players of English Premier League. There is one text file for each football club. We want to push all this infomration in a Excel Workbook. There are 2 ways of pushing the data into excel workbook. Either push all the text files information into a sngle work sheet in a workbook OR create a work sheet for each text file and then push the data in corresponding work sheet.
Now, I have 3 text files in F:\MyStuff\EPL folder. All these 3 text files store name of a player, his country and number of goals scored. So in total there are 3 columns in all thes 3 text files.
I have a excel workbook EPL.xls in F:\MyStuff\EPL folder and all the records are to be pushed into this workbook. By default there are some work sheets (Sheet1,Sheet2..). Keep only one sheet and delete others. Rename the only sheet as SAMPLE and add three columns to it: NAME,COUNTRY,GOALS (Why to add columns??..Well, I will explain it later). Declare 3 variables FileName- to capture the name of the text file inside for each loop, SheetName- is used to create the name for excel sheet to be created and SQL- is used to frame a Create Table Statement for creating the excel sheet in workbok Exercise1: Lets bring all the information from 3 text files into one WorkSheet. Grab a For Each Loop Container and configure it for the text file as shown below
Then click on the variable mappings and select the variable FileName as shown below
Take a Data Flow Task (shown as DFT_ALL_IN_ONE in the control flow) inside the For Each Loop container. Take a flat file source inside Data Flow Task and double click it to open the Flat File Source Editor. Click on New to create a flat file connection manager which points towards one of the text files in F:\MyStuff\EPL folder and then complete the flat file source configuration hitting Columns tab and then OK. Now we are ready with Flat file Source. Next step is to take a Excel Destination. Create an excel connection manager (EXCEL_SINGLE) which points towards the excel workbook. Then configure the excel destinaitonas shown below
Use data access mode as Table or View and select the sheet SAMPLE$. If you remember we have added three columns to the sheet SAMPLE in the destinaiton excel workbook. We added 3 column because the text files contain three columns and we have to take those columns from text files to the excel work sheet. Then click on Mappings tab in the left pane and complete the column mappings between Input column and Destination column. If you get a small red X mark on Excel Destination after completing the configuration, either use a Data Conversion component after Flat File Source or go to Advanced Editor of Flat file source (by rt clicking the flat file source) and select the Input and Output properties. Then select Output Columns and change the datatype as required.

Exercise2: Lets create a work sheet for each text file and move the data from each text file to a different excel work sheet. In the above control flow, there is a execute sql task (EST_CREATE_SHEET) which is responsible for creating the worksheets in a work book. So take a execute sql task inside the for each loop and select the Connection type as EXCEL. Then select the excel connection manager created as a part of exercise1 from the drop down box as Connection. Select SQL Source type as variable and select source variable as SQL. Execute SQL Task editor will look like: Now lets see what this SQL variable is all about?SQL variable is used to store the CREATE TABLE statement which will create excel work sheets inside the excwl workbook. Select the variable SQL and set an expression for it as:
"CREATE TABLE " + @[User::FileName] + " (Name VARCHAR(40),Country VARCHAR(40),Goals INT)"
Thisexpression will create a work sheet at each iteration of the for each loop with the name as the name of the text file which gets captured in FileName variable.Aftre this take a Data Flow Task and configure it as the first data flow task was configured only difference is the data access mode in Excel Destination. This time access mode will be Table name or view name variable as shown below: SheetName is the variable that will store the excel worksheet name where data is to be transfered. Select the variable SheetName and set its "Evaluate as Expression" property to true. Write an expression as: @[User::FileName] + "$"
Execute the package and check the results in excel workbook.