Better performance to load 6.5 million records from flat file to OleDb destination (i.e. database table)

Question

Scenario

I am using Visual Studio 2019 to load data from flat files (txt files) into SQL Server 2019 database tables. These are staging tables.

I have 10 text files and need to make one package per file to load data in 10 different tables (staging) dedicated to each file.

This is a requirement of my task.

I am able to do it successfully. All packages have a fairly fast execution time except one in which the text file has around 6.5 million records and the package execution time to load the data into database table is 2 minutes 30 seconds.

For this task, I have used a Data Flow Task which contains:

1. Flat file Source - This contains the file from which data has to be loaded.
2. OLE DB Destination- For the database table to which data has to be loaded. (attached screenshot for reference)

What do I wish to achieve?

I want to improve the performance of this package to reduce the execution time as much as possible.

Answer 1

If you are looking to insert a flat-file into an SQL Server table without any transformation. You can use the SSIS BULK INSERT Task. Or simply you can use a SQL BULK INSERT command. Example:

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\items.csv'
   WITH
      (  
         FIELDTERMINATOR =';'
         , ROWTERMINATOR ='\n'
      );

Answer 2

Thanks a lot for your advice and suggestions. It helped me immensely.

I did iterations for different combinations of data flow properties like DefaultBufferSize, DefaultBufferMaxRows and the number of output paths from the 'Balanced Data Distributor' (screenshot attached). Now the package executes in 15 seconds.