Load Data From Database To Text File Using SSIS Package

Let’s discuss how to connect the database to SSIS package and how to load the table record(s) from database to notepad file. We can pull it into notepad, excel, csv or any other extension that you want to store.

Let’s discuss how to connect the database to SSIS packages and how to load the table record(s) from the database to Notepad files. We can pull it into Notepad, Excel, CSV or any other extension that you want.

Make sure you have installed Visual Studio 2015, Business Intelligence, and SQL Server on your machine.

Step 1

Go to New Project - click on “Business Intelligence” - select “Integration Service Project”. Then, you can see that the below window will show up on the desktop.

SQL Server

Step 2

Set the name for the Project and click on “OK”. Find the below screenshot.

SQL Server

Step 3

Post loaded your application. You can see Solution Explorer on the right side of the page and SSI Toolbox will appear on the left side of the page.

SQL Server

Step 4

In the bottom of the page, you can see the “connection manager” window. Right-click on the window; a pop-up menu will appear. Find the below screenshot.

Then, click on “New OLEDB Connection” to create new back-end connections. You can connect different types of back-end such as SQL Server, Flat File, ADO.NET, and Service configuration.

SQL Server

Step 5

Click on the New button and connect your own connection. In the below diagram, I already have connected with multiple connections for my projects.

                                     Click on the “New” button.

If you want to delete the existing one, there is an option called “Delete” on the window. If you want to remove all works, hit the Cancel button on the window.

Step 6

Set the server name and authentication details on the Connection Manager. Then, select your database name from the list and click on “Test Connection” to validate your current connection.

Click “OK”.

Once you created the connection, you can see that on the connection manager window in the bottom of the page.

Step 7

Select and drop the Data Flow Task list from the SSIS Toolbox.

SQL Server

Double click on it. 

Step 8

You will see the below window on the screen. So, you choose your table from the list, which you want to load into the text file.


 
Step 9

Then, select the “Flat File Destination” from the Toolbox destination list. Drag and Drop it on the package file.

SQL Server

Double click on the “Flat File Destination” task. It will open the new window to add the file destination.

Find the below screenshot, 

Point #1 - Add “New” file to the package manager.

Point #2 - Browse the file from your local/server machine.

Point #3 - Select the file or enter the file name to store data.

SQL Server

Step 10

You can see that the empty file has been created on the machine. Have a look where you set as a destination.

SQL Server

Step 11

Right-click on the package file and click on “Execute Package”. The package will ready to execute it.

SQL Server

So, here, you will get the green tick marks on the packages, which means the records has been moved from table to text file.

SQL Server

In this example, around 115,897 records have been moved to the text file.

Step 12

Finally, you can see that all the records have been successfully loaded on the text file. If you want to load the data into Excel or CSV or any other extension, just update the destination files on the connection manager.

Find the below image for reference.

SQL Server

Hope this helps. Please let me know if you have queries.

Thank you!