Load the content of many Excel files and populate the column of the department based on the filename-CodePudding

I have the following Excel file :

I have 5 exact files with the same structure, I managed to use a fooreach loop container to load all the data in the 5 Excel files in the same table (SQL Server)

In the department column, I want to store the ID of the department based on the filename If data is coming from d001.xls the departmentID will have 1 as a value in all the records.

CodePudding user response：

I assume that you used a Foreach Loop Container to load all the files dynamically so in the same container, create a local variable:

The variable is configured like below :

and in the Data Flow, add a Derived Column with the following expression :

LEFT(RIGHT(@[User::strExcelFile],5),1)