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.
Example of Export Data From SQL Server To Excel in SSIS
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
|
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.
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
Leave a Reply