ETL – How to read data from Excel files

By | 09/09/2019

In this post, we will see how to read data from an excel files and how to save data into db.

We start to create a database called Test and then, using this script, a table called TabExcel:

USE [Test]
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TabExcel](
	[Id] [int] NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Surname] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO



Now, we open Visual Studio 2019 and we create an Integration Services Project:

First of all, we define five project parameters:
1) pathFile for the directory where ETL will take the excel files
2) username for the DB username
3) password for the DB password
4) servername for the DB server
5) db for the db used to save data

FOLDER WITH TWO EXCEL FILES


FILE EXCEL’S STRUCTURE

Now, we create a new SSIS package called ReadFile.dtsx and add two variables called pathFileExcel and fileNameExcel:

Then, we insert a Sequence Container and we add a Script Task in order to pass the value of project parameter pathFiles, into the variable pathFileExcel:


public void Main()
{
    // TODO: Add your code here
    Dts.Variables["User::pathFileExcel"].Value = Dts.Variables["$Project::pathFiles"].Value;

    Dts.TaskResult = (int)ScriptResults.Success;
}



Now, we add a Foreach Loop Container for reading the Excel files:

Then, we add a Data Flow Task where we will insert an Excel Source and an OLE DB Destination:

We define the OLE DB connection to manage the connection with Test DB, using these parameters:

and an Excel Connection, called Excel Connection Manager, using this parameter:

Finally, we define the Excel Source:

and the OLE DB Destination:

We have done.
Now, we can run the ETL:

and with the query “select * from TabExcel“, we can verify the execution:



Leave a Reply

Your email address will not be published. Required fields are marked *