How to Import data from excel into SQL Server database using SSIS?

Problem: How to Import data from excel into SQL Server database using SSIS?

Solution:

SSIS Is a Tool available from Microsoft SQL Server Installation. Which would help in the data transfer from different formats of sources to available destinations.

You can download SSDT or BIDS To get SSIS.

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

From the SSIS Toolbox we are going to use few components

      1.     Connection Managers. – Used for authentication of the connections
2.     Data flow Task – allows to move data from source to destination
3.     Excel Source – connection to browse the source file.
4.     Ole DB Destination – allows to map the destination
5.     Lookup Component. – allows to check if there is some existing data with same information
6.     Precedence Constraints. – connectors in between the task.

Example: I have a spread sheet which has few rows and around 8 columns with headers. Which I would like to import inside to SQL server.

Step 1: go to the control flow and create a data flow task.

Step 2: Move on to the data flow area and create an excel source and Connection managers for excel. Create an OLE Db destination and connection mangers.

Step 3: Execute SSIS package or Task from the control flow.