Move Data From Excel To SQL Using SSIS

Introduction

In this article, we will learn about how to move data from excel to SQL Using SSIS. SSIS is known as SQL Server Integration Services. The 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.

What is SSIS?

SSIS is 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 are a common term in data migration and Business Intelligence.

So, our requirement here, we need to move data from the given excel file to the SQL server data table using SQL Server Integration Services.

Excel File Data

SQL Table data

Step 1. Create a Project

Create a new project in Visual Studio 2019 and select Integration Service Project.

Give the project name and send the saved location.

SSIS default project structure looks like this, default you see there is one dtsx file added, you can rename it or you can add a new file in Solution Explorer.

When you click on the default package, that looks like the given screenshot.

What is 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, connecting to FTP and containers, and constraints to join flows.

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.

Select the OLEDB connection manager type.

Click Add and click the new connection and enter the server name, authentication, user name, password, and select database.

Step 3. Create a Task

Let’s create a task to create an empty table in SQL if not exists with columns. If the table already exists then delete the existing table.

Drag and drop Execute SQL Task.

Go into task properties and select the connection name and give SQL statement like this,

This will first delete any table called [SalesOrders] which already exists and then create a new, empty one. Here’s what the Execute SQL task dialog box now looks like,

Step 4. Create an Excel Connection

To import data from the Excel workbook, you need to create a connection to it and select the Excel connection type.

Browse the Excel file path and Excel version and check if the file's first row has column names or not and click ok.

Step 5. Create a Data Flow Task

What is 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 the data flow task from the toolbox.

Double-click on Data Flow Task and Drag and drop Excel Source from the toolbox.

Double-click on Excel source and select Excel connection manager and the Name of the Excel sheet and map columns.

Now drag and drop the OLE DB destination.

Double click on OLE DB Destination and select connection manager and name of the table and mapping for columns.

Now execute the task,

As you can see both tasks ran successfully, now check in the SQL Server database if the table is created and data in inserted.

Conclusion

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