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
STEP 2: Double click on the 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.
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
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, 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
From the below, you can observe that We decided the DimGeo text file located in my local hard drive.
Click on the columns tab to verify the column names
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.
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
Let us check the result by opening the DimGeo text file