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 SQL server database and export SQL Server table data to EXCEL file in local hard drive.

Export Data From SQL Server To Excel in SSIS Example

STEP 1: Drag and Drop the Data Flow Task from tool box to control flow region and rename it as Export Data From SQL Server To Excel

STEP 2: Double click on data flow task will open the data flow region. In 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 SQL Server connection manager and also to select the required data from the table.

SQL Statement we used above is

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.

TIP: If we don’t want any column then don’t add them in your SQL command.

By clicking ok we are done with our source part.

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

Here we selected the DimGeography.xls as our excel destination sheet

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

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

Here we selected the DimGeography table 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 same then, the intelligence will automatically map. If there is any changes in the column names then we have map them manually.

Once you done with mapping click on ok button to finish the project development. Let us run the package and see

Let us open the excel file and check the result

Thank you for Visiting Our Blog