Visual studio 2019- Better performance to load 6.5 million record from flat file to ole db destination (i.e. database table in ssms 2019)

Lovedeep Singh 11 Reputation points
2020-12-17T05:39:53.243+00:00

What is the scenario:

  • I am using Visual studio 2019 to load data from flat files (txt files) to database tables in SSMS 2019. These tables 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 db 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.

48957-image.png

I can provide more details if required.
Thank you.

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,643 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,116 Reputation points
    2020-12-17T13:23:27.74+00:00

    I have two ideas for you to try:

    1. Install and use Microsoft OLE DB Driver for SQL Server. It is the latest version, and available since 2018. Download link: oledb-driver-for-sql-server
    2. Increase network packet size up to 32767 on that new driver connection.
      Here is how to do it: Performance Best Practice: Network Packet Size
    1 person found this answer helpful.
  2. Monalv-MSFT 5,896 Reputation points
    2020-12-17T07:56:40.357+00:00

    Hi @Lovedeep Singh ,

    The OLE DB destination provides five different data access modes for loading data:

    1. A table or view. You can specify an existing table or view, or you create a new table.
    2. A table or view using fast-load options. You can specify an existing table or create a new table.
    3. A table or view specified in a variable.
    4. A table or view specified in a variable using fast-load options.
    5. The results of an SQL statement.

    Please use fast-load options when choose data access mode in OLEDB Destination.

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?