How to Export Data to Excel?

Scenario

You need to export vendor list data from the List. The SSIS package consists of two dataflow tasks, one for the vendor and one for VendorAddress. The vendor list is mapped to two tables - Vendor and Address. The data from these tables is exported to an Excel file called VendorDetails.xls. 

1. Open SQL Server Business Intelligence Development Studio.

2. In Control Flow, drag and drop the Dataflow task from the Toolbox.

   

3. Click the Dataflow tab of the Vendor data flow task.

   

4. Drag and drop the List Source data flow component.

5. Double-click the List source to open the List Source property window.

   

6. Next you need to set the properties. Select the Repository name.

7. Select owner from the drop-down list.

8. Enter the Password.

9. Select Vendor from the list names.

10. Select the Vendor Table from the List Structure tree.

    Note: Checking Export all columns option will check all the columns. 

11. Click OK to save.

12. Drag the Excel Destination component. Link the List source with the Excel destination.

13. Next, you need to map the columns. 

14. Right-click the List Source component and select the advanced editor. 

15. Go to column mapping and map the corresponding columns from List source column to Excel sheet destination columns.

    

16. Go to control flow design dataflow for Address. 

17. Drag and drop the Dataflow task and rename it as Address.

18. Go to dataflow task. Drag and Drop List Source component and configure the properties as explained in the above steps.

    

    

    

19. In the Package diagram, click on the Start Debug option in the BID to execute the package.

    

20. You have successfully exported data from the vendor list to VendorExport.xls.