Export Data from SQL to Excel Sheet using SSIS
Exporting Data from SQL table to Excel is a simple process using SSIS.
Below are the Steps that needs to be followed
First create a table in your database using below SQL script.
CREATE TABLE [dbo].[Employee](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[Employee] ON
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
SET IDENTITY_INSERT [dbo].[Employee] OFF
Requirement- we will export the Data of this sql table into excel file
Open SSIS follow the below Step one by one.
Open BIDS(Visual Studio)
Click on New Project
Select Business Intelligence Project
Select Integration Service Project
Right click SSIS Packages
Create new SSIS Package
Give a name to the Package otherwise by default it will take Package1.dtsx
1-Go to Control flow(Left Side)
2-Drop a data flow Task
3-Name the Data flow Task as Exporting Excel Data (Optional ) as below Screenshot.
Data Flow Task
1-Now open the Data flow task by double clicking on it.
2-Drop OlEDB Source
3-Drop Excel Destination as below Screenshot
Creating Connection for Source and Destination
1-Double Click OlEDB Source
2-Create a connection with your SQL Database as below screenshot.
3-Next we will create connection with excel Destination
4-Double click Excel Destination
5-Click on New(Excel Connection Manager)
6-Select the Path where you want to keep you file.
7-Give Name for the output file as ExcelData.xls
8-Now we will create sheet in the excel file.
9-Click on New(Name of Excelsheet)
10-It will automatically create a sheet for you with the name Excel_Destination
11-Once done click ok
Next we will connect the Oledb Source with Excel Destination by dragging the blue arrow from Oledb source to Excel Destination
Once source and destination connected Run the Package by Right clicking the package and executing it.
Open Excel sheet to verify the Result. The data from Employee table has been exported to excel file as below screenshot.