ADO.NET source export data very slowly

Nicky 116 Reputation points
2021-03-18T14:37:18.267+00:00

Hi

I am using ADO.NET Source in Data Flow task. I have to transfer more than 1 million rows, cannot use OLEDB source because of encrypted data.

I have set Default Buffer Max Rows - 40000
Default Buffer Size - 16777216

Still data transfer is very slow.

I will really appreciate any help on this.

Thanks !

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 24,946 Reputation points
    2021-03-18T15:29:08.347+00:00

    Hi @Nicky ,

    What about the latest Microsoft® OLE DB Driver 18 for SQL Server?
    It is available since 2018. Its latest version 18.5
    All other OLEDB providers for SQL Server are deprecated now.

    0 comments
  2. Nicky 116 Reputation points
    2021-03-18T15:33:29.707+00:00

    @Yitzhak Khabinsky : Can I read encrypted data using OLEDB ?

  3. Monalv-MSFT 5,891 Reputation points
    2021-03-19T06:46:14.653+00:00

    Hi @Nicky ,

    1.Could you please divide the 1 million rows into different groups?
    Then use one more data flows to load data in the Data Flow Task.

    2.This topic provides suggestions about how to design Integration Services packages to avoid common performance issues. This topic also provides information about features and tools that you can use to troubleshoot the performance of packages.
    Please refer to Data Flow Performance Features.

    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.