Export Data from SQL Server to Flat File in SSIS

In this article, we show you the step by step approach to Export Data from SQL Server to Flat File in SSIS. For this, we are going to create a package to select the data from the SQL server database and send that data to the text file in a local hard drive.

Export Data from SQL Server to Flat File in SSIS Example

STEP 1: Drag and Drop the Data Flow Task from the toolbox to the control flow region. And rename it as Export Data from SQL Server to Flat File

SSIS Export Data from SQL Server to Flat File 1

STEP 2: Double click on the SSIS data flow task will open the data flow region or tab and then drag and drop the OLE DB Source and Flat file destination into the data flow region.

SSIS Export Data from SQL Server to Flat File 2

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

Export Data from SQL Server to Flat File in SSIS 3

SQL Statement we used here is

USE [AdventureWorksDW2014] 
GO 
SELECT TOP 1000000 [CountryRegionCode] 
                   ,[EnglishCountryRegionName] 
                   ,[StateProvinceCode] 
                   ,[StateProvinceName] 
                   ,[City] 
                   ,[PostalCode] 
                   ,RESELLER.[UnitPrice] 
                   ,RESELLER.[ExtendedAmount] 
                   ,RESELLER.[ProductStandardCost] 
                   ,RESELLER.[TotalProductCost] 
                   ,RESELLER.[SalesAmount] 
                  ,RESELLER.[TaxAmt] 
FROM [DimGeography] 
INNER JOIN 
     [FactResellerSales] AS RESELLER ON 
[DimGeography].[SalesTerritoryKey] = RESELLER.[SalesTerritoryKey] 

ORDER BY [CountryRegionCode] 
        ,[EnglishCountryRegionName] 
        ,[StateProvinceCode] 
        ,[StateProvinceName] 
        ,[City] 
        ,[PostalCode] 
        ,RESELLER.[SalesAmount] 

STEP 4: Click on the columns tab to verify the input columns and then click ok

Export Data from SQL Server to Flat File in SSIS 4

By clicking ok we are done with our source part.

STEP 5: Let us configure the destination by double-clicking on the flat file destination.

Export Data from SQL Server to Flat File in SSIS 5

We have not created any Flat File Connection Manager before So, Click on New button will open a window form to select the Flat File Format as shown in below image

Export Data from SQL Server to Flat File in SSIS 6

From the above screenshot, we chose the Delimited option. By clicking the Ok button will open the Flat File Connection Manager Editor to select and configure the text file.

By clicking on the Browse button, we can select the destination text file

Export Data from SQL Server to Flat File in SSIS 7

From the below, you can observe that We decided the DimGeo text file located in my local hard drive.

Export Data from SQL Server to Flat File in SSIS 8

Click on the columns tab to verify the column names

Export Data from SQL Server to Flat File in SSIS 9

Click ok to create a new flat file connection to the file we want to use as the destination.

STEP 6: Check the SSIS Export Data from SQL Server to Flat File mapping tab whether all the rows exactly mapped to the destination columns or not.

Export Data from SQL Server to Flat File in SSIS 10

Once you complete with mapping, click on the Ok button to finish the project development.

Let us run the Send Data from SQL Server to Flat File in SSIS package and see

Export Data from SQL Server to Flat File in SSIS 11

Let us check the result by opening the DimGeo text file

Export Data from SQL Server to Flat File in SSIS 12