Export Data from SQL Server to Flat File 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 Flat File

STEP 2: Double click on 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 data flow region.

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.

SQL Statement we used here 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 25 26

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

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.

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

From the above screenshot you can observe that, We selected the Delimited option. By clicking Ok button will open the Flat File Connection Manager Editor to select and configure the text file.

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

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

Click on the columns tab to verify the column names

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

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

Once you done with mapping click on ok button to finish the project development.

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

Let us check the result by opening the DimGeo text file