Import data from excel to SQL in SSIS

In this tutorial you will learn how to import Data from Excel to SQL table using SSIS service

Extract Data from Excel to SQL

Fetch data from excel file, filter data and insert into SQL server datable, and delete duplicate data using SSIS

Create SSIS Package

Here we create a SSIS package with three tasks in it, two SQL Task and one Data Flow Task. Each Task has some responsibility, Here is the flow

  • Execute SQL Task
    In this SQL script we clean the destination table, or if the destination table does not exist then we create the table
  • Data Flow Task
    In this task we map the excel file (data source), fetch data from there, ceate ole db connection with datatable, pull data from excel to datatable, we use a data conversion control to set the right data type, so that data type does not create any problem. Learn more about Data Flow Task in SSIS
  • Execute SQL Task
    This is again an SQL Script Task, where we remove all duplicate data. Learn more about SQL Task in SSIS
Data Flow Task Design SSIS

Set up flow from your excel data source to SQL Database destination, you need to use OLE DB connection component to connect your SQL Database, and the data conversion flow will come in between, because we want data type to be converted before we insert data into SQL table.

data flow task

Data Conversion in SSIS

Data Conversion will be required because some data types in excel sheet may not be compatible with sql data types, so this conversion process will help in handling any such situation.

ssis Conversion

Extract data from Excel to SQL table using SSIS
SSIS Interview Questions Answers
SSIS Examples