Data Transfer From SQL Server to Excel

In this article, I will share information on how to transfer data from any table in our database to a custom-designed Excel file using the SSIS tool provided by MSSQL developers.

First and foremost, to enable our development through Visual Studio, we need to install Microsoft SQL Server Data Tools on our computer.

You can find helpful information about this installation in the following link.

I personally used this installation in conjunction with Visual Studio 2019, so you should also install SSDT based on the version of Visual Studio you are using.

Once the installation is complete, you will need to restart your computer. After the restart, the SSIS tool will become available for use within Visual Studio.

To create a new project in Visual Studio, follow these steps:

1. Open Visual Studio.
2. Go to "File" -> "New" -> "Project."
3. In the project templates, select "Integration Services Project."

This will allow you to create a new SSIS project within Visual Studio. 

The purpose of our project is to extract data from a table in the SQL Server database and transfer this data to an Excel file. 

After creating the project, I added an element called "Sequence Container" to the Control Flow window that automatically opens to facilitate tracking developments. 

Think of the Control Flow as an area where you control the workflow. Here, you can define priority sequences and perform logical operations related to the flow of tasks.

The "MSSQL To Excel DB Process" is a Data Flow Task object. This object is used to manage the ETL (Extract, Transform, Load) process. Within this process, we will handle tasks like extracting data from different sources, applying transformation operations, and finally writing the data to the target destination.

To define data exchange configurations, you can right-click on this object and select the 'Edit' tab to switch to the relevant screen.

When you open the Data Flow object, you start by selecting the 'OLE DB Source' component, which is necessary for connecting to the database and specifying the data source. This is the initial step in the process.

After that, you can make connection settings by double-clicking on the OLE DB Source. At this stage, you can select the table and, if desired, preview the data using the "Preview" button.  

Once you have completed the database connection settings and source selection, you can start designing the Excel file as you desire. Based on your source table, you can create a template as shown below:    

In the next step, you can return to the package development in Visual Studio and add the Excel Destination object to the Data Flow Task flow using the toolbox.

To prevent potential error risks arising from differences in column formats between the Excel file and the database, it's beneficial to perform a Data Conversion process. This helps ensure that the data is properly transformed to match the target format.

Therefore, we added the Data Conversion component found in the toolbox to the data flow diagram, and our final workflow looks like this:  

I'm double-clicking on the Data Conversion component to convert the columns from the source to the expected data types as required by the application.

In the next step, you can double-click on the Excel Destination to manage Excel operations. In the Excel Connection Manager section, you can use the 'New' button to create a new connection and select your Excel file.

Once the selections are made, you will notice that the columns in your Excel file are automatically loaded into the Mappings tab. In this section, you can perform source table and Excel column mappings.

You should continue the process with the newly created (transformed) fields from the Data Conversion.

By completing this process, you have successfully mapped the source table from your database to the Excel file. You can now initiate the project to see the data being transferred to the Excel file. 

In this project, we have successfully performed data export. We've reached the end of this article. I hope it has been helpful to you. Goodbye! :)