Hi All,

I'm new to SSIS. I have 2 different excel files file1 and file2. file1 should be loaded to table1 and file2 should be loaded to table2. Both of the files will have 1 sheet inside. Do I need to create separate excel source for file1 and file2? I mean file1 in one excel source and that will be connecting to 1 execute sql task. file2 in other excel source and that will be connecting to another execute sql task. Is this the way I should proceed or is there any looping should be done?  I need to schedule this activity to run every week. So, I'll get new files every week with the same file names and sheet names. Do I need to consider anything for this requirement also?

I'm planning to do truncate and reload not an incremental load.

Please help me on this. It would be great if you can give me a step by step solution for this since I'm new to this.

Regards,

Julie

It depends on the metadata (structure ) of the sheets. If both file sheets have same columns you can use a for each loop container with a single data flow task.  Otherwise you would need two separate data flow tasks for each file. In SSIS the metadata for a data flow cannot change during runtime and it will be fixed at design time when you map from source to destination component.

Hi,

you can also use Script task,

http://www.codeproject.com/Questions/767979/How-to-import-multiple-excel-files-with-different

Regards,

Manish

Hi Julie,

According to your description, you want to truncate and reload data from two different excel sheet to two different tables, not an incremental load.

Just as Visakh said, if both file sheets have same columns, then we can use a for each loop container with a single data flow task.  Otherwise we would need two separate data flow tasks for each file. Each Data Flow Task load data from an Excel Source to an OLE DB Destination. Since you need truncate and reload, not an incremental load, we can add an Execute SQL Task to truncate the destination table with the query below before each Data Flow Task:
TRUNCATE TABLE [TABLE_NAME]

The following screenshot is for your reference:

Thanks,
Katherine Xiong

I think you'll find your solution here.

https://www.youtube.com/watch?v=1WXKpkwjhX8