Help Needed -- Dynamically Defining Files In Connection Manager

I am pretty new to SQL Server 2005 and SSIS. I am trying to dynamically load files into SQL Server 2005 using files/paths contained in tables. I have a key table in my SSIS package that defines which files should be processed, some default values that will be associated with each file (ie CompanyKey and PeriodKey) and the file path (see below for example):











CompanyKey

PeriodKey

ProcessFlag

FileName


2

61

FALSE

2005TB200501.xls


2

62

FALSE

2005TB200502.xls


2

63

FALSE

2005TB200503.xls


2

64

TRUE

2005TB200504.xls


2

65

TRUE

2005TB200505.xls


2

66

TRUE

2005TB200506.xls


2

67

TRUE

2005TB200507.xls


2

68

TRUE

2005TB200508.xls


2

69

TRUE

2005TB200509.xls


2

70

TRUE

2005TB200510.xls


2

71

TRUE

2005TB200511.xls


2

72

TRUE

2005TB200512.xls

The package I am developing is supposed to loop through this table checking for files with ProcessFlag = TRUE. For those files, it will load all of the records/columns (prefaced with the company and period keys) into a common table.

Do I have to manually create data sources for each file or is there a way to dynamically define the connection and process the connection?

Any assitance you could provide would be greatly appreciated!

Thanks.
Vik