Tutorial Gateway

  • C Programming
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • About
    • About Us
    • Contact Us

Export Data From SQL Server To Excel in SSIS

10-04-2015 by suresh Leave a Comment

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

SSIS Export Data From SQL Server to Excel 1

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.

SSIS Export Data From SQL Server to Excel 2

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.

SSIS Export Data From SQL Server to Excel 3

SQL Statement we used above is

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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.

SSIS Export Data From SQL Server to Excel 4

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

SSIS Export Data From SQL Server to Excel 5

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.

SSIS Export Data From SQL Server to Excel 6

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.

SSIS Export Data From SQL Server to Excel 7

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

SSIS Export Data From SQL Server to Excel 8

Let us open the excel file and check the result

SSIS Export Data From SQL Server to Excel 9

Thank you for Visiting Our Blog

Placed Under: SSIS

Share your Feedback, or Code!! Cancel reply

Trending

  • SQL Server Integration Services (SSIS)
  • SQL Server Tutorial
  • C Program to Print Prime Numbers from 1 to 100
  • C Program to Calculate the Sum and Average of n Number
  • SQL Server Reporting Services (SSRS)
  • Home
  • Types of Functions in C

Stay in Touch!

Sign Up to receive Email updates on Latest Tutorials

  • Blogger
  • C Programs
  • Java Programs
  • SQL
  • SSIS
  • SSRS
  • Tableau
  • JavaScript

Copyright © 2018 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy