Store filename in variable and create tables with the filename in SSIS

Question

I've few excel source files in one folder in SSIS. I want to pull data from these excel files and load in to SQL tables. My problem is I want to save all the files names one by one and want to create SQL table with exactly same name as filename and then want to load each excel file in corresponding table. Please help me how to create a package for this.

Answer 1

Jayvee has presented the high level view which is good enough! Let me add in bit detail.

I am assuming that you have dynamic Excel file connection.

1. Declare a variable and named it as FileName. And assign it the first file name which is available in the folder.

2. Place Foreach Loop Container and double click on it. Specify the Folder: and Files: as shown in image below.

3. In the same Foreach Loop Editor, go to Variable Mappings. Select Variable from drop down list. This is the same variable which we defined in first step. Set its Index to 0. Click OK.

4. Remaining task is same as Jayvee explained.

See this link for further help. And this for Result Set Property Not Set Correctly. I think setting ResultSet property to SingleRow will do the job.