Export Data from SQL to Excel Sheet using SSIS

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]

GO

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

Control Flow

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.

Write to comment box if you are facing any issue while exporting the data from sql to excel file.