How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet - SSIS tutorial

Let's say you are working for Auto Insurance Company as ETL developer, you get different excel files from different regional office those you need to load to Dbo.Customer Table in TechBrothersIT database.

Here is the definition of SQL server table.

CREATETABLE dbo.Customer (
id INT
,name VARCHAR(50)
,dob DATE
)
GO

But you often face problem as you don't always get the exact columns.Sometime you get a nice excel file with Id,Name and Dob and often you get the file with only Name column and sometime you get the file with Id and name.

You notice from above, you will be always getting the file with correct column names but sometime less or more or sometime with exact same number.

Consider, I got three below files as shown below and need to load to dbo.Customer Table.

You notices that I did not mention the name of Sheet, It can be anything as long as it has only one sheet. As for this scenario, I am considering there will be always single sheet.

How to load Excel files dynamically to SQL Server Table when Column are not exact Same in SSIS

If you notices that, in some Excel files, the sheet has columns in order as we have in SQL Server Table. In some of them the order of columns is messed up and even in some of them the columns are missing.

Now the questions is , How to Load these different sheets to single Table in SQL Server Table, What solution you will propose in this situation as ETL Developer?

Solution:

We can't handle above situation with builtin Excel Source as number of columns are not constant in our sheets. Also the Sheet name is not confirmed and we have to work hard to make that dynamic by using expressions. I will suggest to handle this situation in Script Task.

Step 1:

Let's create two variables.

FolderPath: that will contain the folder path where our files will be dropped or put.

TableName: Provide the table name.

By using above variable, we are making our SSIS Package dynamic. You can simply change values for two variables and start loading the data to new table from your excel files. Also this is really helpful when you are deploying your SSIS Package to QA,UAT and Prod as Folder Path is often different in each environment.

Save the code and exit Script task. Run the SSIS Package and go to SSMS and check the data in the table.

How to load data from Excel file dynamically in SQL Server Table by Script Task in SSIS Package

As you can see that the data from three files with single sheet is loaded to our table. As you don't want to load the files again, it is good idea to move the files to some other folder after loading. you can check this link to see how the files can be archived. You can use Script Task itself or File System task to archive the files after loading.

Video Demo How to Import Excel Files to SQL Server Table with Exact Number or columns or less Columns in Excel Sheets

Check out our other posts/videos for Dynamic Excel Source and Destination