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 SQL server database and send that data to the text file in local hard drive.
Export Data from SQL Server to Flat File 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 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 in to 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 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
Thank you for Visiting Our Blog