Export Data From SQL Server To Excel in SSIS

In this article, we will show you how to Export Data From SQL Server To Excel in SSIS. For this, we are going to create a package to select the data from the database and export the table to the EXCEL file on the local hard drive.

Export Data From SQL Server To Excel in SSIS Example

STEP 1: Drag and Drop the Data Flow Task from the toolbox to the control flow region and rename it.

STEP 2: Double click on the SSIS data flow task that will open the data flow region. In the data flow region, drag and drop the OLE DB Source and Excel file destination.

STEP 3: Double click on OLE DB Source to configure the connection manager and also to select the required data from the table.

The SQL Server Statement we used above to Export Data To Excel is shown below.

USE [AdventureWorksDW2014] 
GO 

SELECT [EnglishCountryRegionName] 
      ,[StateProvinceName] 
      ,[City] 
      ,[PostalCode] 
      ,SUM (RE.[UnitPrice]) AS [Unit Price] 
      ,SUM (RE.[ExtendedAmount]) AS [Extended Amount] 
      ,SUM (RE.[ProductStandardCost]) AS [Product Standard Cost] 
      ,SUM (RE.[TotalProductCost]) AS [Total Product Cost] 
      ,SUM (RE.[SalesAmount]) AS [Sales Amount] 
      ,SUM (RE.[TaxAmt]) AS [Tax Amount]
FROM [DimGeography] 
INNER JOIN 
     [FactResellerSales] AS RE ON 
[DimGeography].[SalesTerritoryKey] = RE.[SalesTerritoryKey] 

GROUP BY [EnglishCountryRegionName] 
        ,[StateProvinceName] 
        ,[City] 
        ,[PostalCode]

ORDER BY [EnglishCountryRegionName] 

For this example, we are selecting the [Dim Geography] and [Fact Internet sales] tables present in the [Adventure Works DW 2014] database.

STEP 4: Now, click on the columns tab to verify the input columns, and then click ok. In this tab, we can uncheck the unwanted columns also.

By clicking ok, we have done with our source part.

STEP 5: Let us configure the destination by double-clicking the Excel file destination

Here we selected DimGeography.xls as our Excel destination sheet.

TIP: You have to create the Excel sheet before using it in the Excel Connection Manager, and the Excel file should not be open while running the package.

TIP: If you checkmark the First row that has a column names option, then Column names will be saved in the first row of the selected Excel sheet.

Here we selected the DimGeography table to export Data From SQL Server To Excel. So to check the result, you have to open the DimGeography sheet inside the DimGeography.xls

STEP 6: Check the mapping tab whether all the rows are exactly mapped to the destination columns or not.

NOTE: If your input column names and destination column names are the same, then the intelligence will automatically map. If there are any changes in the column names, we have to map them manually.

Once you complete mapping, click on the Ok button to finish the project development. Let us run the SSIS Export Data From SQL Server To Excel package and see

Let us open the Excel file and check the result.