Move Data From SQL Server To Flat File Using SSIS

Developers often need to move a SQL Server database table data into a flat file for reporting and other purposes. In this project, you will learn how to convert data from a SQL Server and copy it into a flat file using a SSIS package.
 
Here is my SQL Server database table data screenshot,
 
Move Data From SQL Server To Flat File Using SSIS
 
Let’s move this data into a flat file using a SSIS package.
 
Prerequisites
 
Visual Studio 2017 is the prerequisite to work with this article.
 
Follow these steps to create a new project in Visual Studio. If you already have a project, skip these steps.
  1. Create a new Project in Visual Studio 2019.
  2. Select Integration Service Project.
  3. Give Project name and save location.
  4. Click Create.
Go to the Solutions Explorer and see the default project structure. As you can see in the following, one default package is added in SSIS package, you can rename it like exportdata.dtsx.
 
Move Data From SQL Server To Flat File Using SSIS
 
Now click on package to see the default view.
 
Move Data From SQL Server To Flat File Using SSIS
 
Drag and drop Data Flow Task from Toolbox to Control Flow in Package. You can rename it according to your naming convention.
 
Move Data From SQL Server To Flat File Using SSIS
 
Double click on Data Flow Task and drag and drop OLE DB Source and Flat File Destination from Toolbox.
 
Move Data From SQL Server To Flat File Using SSIS
 
Now double click on OLE DB Source to configure a SQL Server connection. Provide connection name, Data access mode. If connection does not exist, then create new connection and select a table name and click OK like the below screenshot.
 
Move Data From SQL Server To Flat File Using SSIS
 
Table or view Data access mode fetch all table data, if you want to use query then select SQL Command like this.
 
Move Data From SQL Server To Flat File Using SSIS
 
Move Data From SQL Server To Flat File Using SSIS
 
Now let’s configure the destination. Double click on Flat File Destination and click New button.
 
Move Data From SQL Server To Flat File Using SSIS
 
We have four Flat File Formats.
  • Delimited
  • Fixed width
  • Fixed width with row delimiters
  • Ragged right
I am going to use Delimited and click OK.
 
Move Data From SQL Server To Flat File Using SSIS
 
Browse a flat file name and select Format and map columns and click OK.
 
Time to execute the package, right-click on Package and hit Execute Package.
 
Move Data From SQL Server To Flat File Using SSIS
 
As you can see, the package successfully executed. Now check the flat file to see the data.
 
Move Data From SQL Server To Flat File Using SSIS
 

Conclusion

 
In this article, we have learned how to move data from SQL Server to a flat file.