Import data from SQL server to EXCEL using SSIS

Using SSIS we can transfer huge number of records from one data source to another within very less time. Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That is the power of SSIS.

STEP - 1

Cretae a new SSIS project

create SSIS projectcreate SSIS projectUntitled.png


 Drag Control: Drag Data Flow Task from Toolbox, to Design interface

Data flow task

Configure Data Flow Task: Once you have Data Flow Task on Design surface, Double click on it, or Right click on task and click on "Edit", it will redirect you to Data Flow Tab

Configure Source Connection: Here, I have selected "OLEDB Source", as we need to import data from Database. Drag "OLE DB Source" to design surface and right click on edit and select "Edit", Find the below figure


First, Enter connection manager name and description.
Then, select Table name which you want to import.
Then, select column names those you want to import.


Configure Destination Connection: Here, I have selected "Excel Destination", as we need to import data from SQL SERVER Database to Excel file. Drag "Excel Destination" to design surface.


Now, we need to design data flow. Here we need to import data from OLEDB Source, I have dragged "Success" (Green Arrow), link to Excel Destination for column Mapping.


By default, SSIS provides mapping for columns which have same name, In this case Column Mapping is ok, so we don't need to do anything. If you want to change mapping, you can do it by just changing links between Input Columns and Destination Columns.