Load Data From Database To Text File Using SSIS Package

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.

Step 2

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

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.

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.

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.

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.

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.

Step 10

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

Step 11

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

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

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.

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

Thank you!