Move Data From Excel To SQL Using SSIS

What is SSIS?

 
SSIS known as SQL Server Integration Services. Basic definition you can say SSIS is the storage file with .dtsx extension that contains your control flow, data flow, connections, variables, parameters, event handlers etc. in SSIS projects.
 
SSIS used to merge, copy, extract and transform data from various data sources. Automates Administrative Functions and Data Loading. Populates Data Marts & Data Warehouses. It helps you to clean and standardize data. The data extraction, transformation, and loading are known as ETL and is a common term in data migration and Business Intelligence.
 
So, our requirement here, we need to move data from given excel file to SQL server data table using SQL Server Integration Services.
 

Excel File Data

 
Move Data From Excel To SQL Using SSIS
 

SQL Table data

 
Move Data From Excel To SQL Using SSIS
 
Step 1 - Create a Project
 
Create a new project in Visual Studio 2019 and select Integration Service Project.
 
Move Data From Excel To SQL Using SSIS
 
Give project name send save location.
 
Move Data From Excel To SQL Using SSIS
 
SSIS default project structure looks like this, by default you see there is one dtsx file added, you can rename it or you can add new file in Solution Explorer.
 
Move Data From Excel To SQL Using SSIS
 
When you click on default package, that looks like given screenshot.
 
Control Flow?
 
The control flow is the main part of the ssis package that contains tasks with functionality like Create backups, execute scripts, execute SQL tasks, connect to FTP and containers and constraints to join flows.
 
Move Data From Excel To SQL Using SSIS
 
Step 2 - Create SQL Server Connection
 
Before going next, you need to create a SQL server database connection. Right click on Connection Manager and click New Connection Manager.
 
Move Data From Excel To SQL Using SSIS
 
Select OLEDB connection manager type.
 
Move Data From Excel To SQL Using SSIS
 
Click Add and click new connection and enter server name, authentication, user name, password and select database.
 
Move Data From Excel To SQL Using SSIS
 
Step 3 - Create Task
 
Let’s crate a task to create an empty table in SQL if not exists with columns. If table already exist then delete existing table.
 
Drag and drop Execute SQL Task.
 
Move Data From Excel To SQL Using SSIS
 
Go in task properties and select connection name and give SQL statement like this,
 
Move Data From Excel To SQL Using SSIS
 
This will first delete any table called [SalesOrders] which already exists, and then create a new, empty one. Here’s what the Excecute SQL task dialog box now looks like,
 
Move Data From Excel To SQL Using SSIS
 
Step 4 - Create an Excel Connection
 
To import data from Excel workbook, you need to create a connection to it and select Excel connection type.
 
Move Data From Excel To SQL Using SSIS
 
Browse Excel file path and Excel version and check if file first row has column names or not and click ok.
 
Move Data From Excel To SQL Using SSIS
 
Step 5 - Create a Data Flow Task
 
Data Flow?
 
The data flow task is used to export data from different sources to different destinations and transform the data if necessary. There is a Data Flow component in the control flow and when you double click the task you have new tasks to import and export data.
 
Drag and drop data flow task from toolbox.
 
Move Data From Excel To SQL Using SSIS
 
Move Data From Excel To SQL Using SSIS
 
Double click on Data Flow Task and Drag and drop Excel Source from toolbox.
 
Move Data From Excel To SQL Using SSIS
 
Double click on Excel source and select Excel connection manager and Name of the Excel sheet and map columns.
 
Move Data From Excel To SQL Using SSIS
 
Now drag and drop OLE DB destination.
 
Move Data From Excel To SQL Using SSIS
 
Double click on OLE DB Destination and select connection manager and name of the table and mapping for columns.
 
Move Data From Excel To SQL Using SSIS
 
Now execute task,
 
Move Data From Excel To SQL Using SSIS
 
As you can see both tasks ran successful, now check in SQL Server database if table is created and data in inserted.
 
Move Data From Excel To SQL Using SSIS
 

Conclusion

 
In this article, we have learned how to move data from Excel file to SQL Server table using Visual Studio Integration Service Project.