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,

 

 

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.

 

 

Now click on package to see the default view.

 

 

Drag and drop Data Flow Task from Toolbox to Control Flow in Package. You can rename it according to your naming convention.

 

 

Double click on Data Flow Task and drag and drop OLE DB Source and Flat File Destination from Toolbox.

 

 

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.

 

 

Table or view Data access mode fetch all table data, if you want to use query then select SQL Command like this.

 

 

 

Now let’s configure the destination. Double click on Flat File Destination and click New button.

 

 

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.

 

 

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.

 

 

As you can see, the package successfully executed. Now check the flat file to see the data.

 

 

Conclusion

 

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