In this post I am going to show how to load Excel files data into SQL Server 2017 database table.
First you have to create a your Excel source file as below.
Then you have to create your destination table in the SQL Server 2017. For that you can use below SQL Script.
Then Create A SQL Server Integration Project in the SQL Server Data tools.
File-->New-->Project-->Business Intelligence-->Integration Server--. Give the Project Name.
Then Brows the Excel file.
Drag and drop the Data Flow Task into Control Flow.Rename As 'Load Excel Data'
First you have to create a your Excel source file as below.
Then you have to create your destination table in the SQL Server 2017. For that you can use below SQL Script.
CREATE TABLE Customer(Your output will be as below.
[CustomerKey] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](255) NULL,
[MiddleName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[EmailAddress] [nvarchar](255) NULL,
[YearlyIncome] [money] NULL
)
File-->New-->Project-->Business Intelligence-->Integration Server--. Give the Project Name.
Create a New Connection under Connection Manager window(Right click on the Connection Manager window).
Select the Excel Type and Click Add.Then Brows the Excel file.
Drag and drop the Data Flow Task into Control Flow.Rename As 'Load Excel Data'
Double click on the Data flow task. Then it will redirect to Data Flow tab.Drag and drop the Excel source from the source menu.
Double click on the excel source and brows the saved location and give sheet name.
Click Ok and Save.
Create OLE DB Connection by right click on the Connection Manager window.
Create a New Connection.
Give the SQL Server database connection details and test Connection.Click Ok.
Drag and drop the OLE DB Source into Data Flow Tab.
Create a link between Excel source and OLE DB destination.
Double click on the OLE Db destination and give the OLE DB connection details and select the destination table in the database.
Go to Mapping tab and map the source column and destination columns
Save the Package and execute.
Now you can see the Excel data has been loaded to SQL server table.
Thank you !!!
SQL Server Integration Service (SSIS) 2017 - Loading Excel data into SQL Server Table
Reviewed by Pubudu Dewagama
on
2:05:00 AM
Rating:
good work bro
ReplyDelete