SSIS Part 152-Load Only Matching Column Data to SQL Server Table from Excel Files Dynamically

How to Load Only Matching Column Data to SQL Server Table from Excel Dynamically in SSIS Package - SSIS Tutorial

Link to website for script used in video Load only matching column data from Multiple Excel files with Single/Multiple Sheets to SQL Server Table

You are working as ETL Developer/SSIS developer for Financial firm. You get Excel files in source folder with single sheet that you need to load into SQL Server Table called dbo.Customer in TechbrothersIT Database.
Here is the definition of dbo.Customer Table.

CREATE TABLE dbo.Customer (
id INT
,NAME VARCHAR(50)
,dob DATE
)

There are some problems with Excel Sheet data
1) Sometime you get exact number of columns in Sheet
2) Sometime you get extra columns in Excel file Sheets
3) Sometime you get less columns than your table definition.
4) Excel File can be provided with any name in given folder
5) Sheet Name can change as well in each Excel file

You want to developer an SSIS Package, that should be able to handle above situation and load all data for columns which match with our SQL Server Table Definition( dbo.Customer).

It should ignore extra columns in Excel sheets and load the matching columns data without error. If we receive less columns in Excel sheet, It should go ahead and load that into table. What would be your approach to developer this type of SSIS Package?

Items you will learn in this video
How to load Data from Excel Files to SQL Server Table by using C#
How to Read Sheet Name from Excel File in SSIS Package
How to read Excel File Name in SSIS Package
How to Check matching Header Column Names to SQL Server Table by using C sharp
How to save scalar string value from SQL Server Query to Variable in C Sharp
How to use BulkCopy to load data from DataTable to SQL Server Table
How to Map Columns Dynamically in BulkCopy C Sharp

[ryanm.]

I really like your tutorials and the way you explain things. Keep up the good work!

[erfanebrahimi]

Wow, just wow. That was an awesome tutorial. Thank you so much.

[kg]

Thank you for this. Your Tutorials are great.

[mollynteh]

Hi! How do we work with txt files instead of excel? Thanks!

[thesoothingbutterfly]

I really appreciate your work. Yours videos have been very helpful to me. I had one query though, I want to dynamically map my source table and destination table if the after creating the package which is taking my ouput query from sql config table and loading it into destination, i change my query again in config query with less columns or more columns now. So i want to dynamically map them with my destination table which is also sql as well. Can you plse help me with it.

[rohitkolekar]

Very Informative ... Appreciate your efforts..

[databasemsbi]

can we use foreach loop plus excel sources dynamically load excel sheet one by one to replace to use script task if we can't write C# for new situation ?

[srvvinay]

Hi
i have one dought
in excel i have data in one column like this " 123-abc-12-12" something like this
while i am extracting to sql server it is taking as null values
Please help me how to load same data to sql server from destination

[snaesan]

What's was the speed to bulk copy million rows? Was it fast?

[nithinkumar]

Getting Error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

[bhishmasingh]

Hi Amir
Can you please send the link for how to load unmatched data from Tables in SSIS?

[krunalkathwate]

Hi. This is really amazing content and thank you so much for the same. Although, I am getting "Compilation errors" while executing the code even after going through with it thrice. Please assist with any idea on how to resolve it.

[IgfernandezBravo]

How can I do this with a csv that has dynamical columns?

[pupunjena]

stop adding an 'A' to each word that u pronounce

[Datapassenger_prashant]

Sorry..but why are u using fake accent