question

nikz2246 avatar image
0 Votes"
nikz2246 asked Monalv-msft edited

ADO.NET source export data very slowly

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @nikz2246,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

nikz2246 avatar image
0 Votes"
nikz2246 answered YitzhakKhabinsky-0887 edited

@YitzhakKhabinsky-0887 : Can I read encrypted data using OLEDB ?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@nikz2246,

This latest Microsoft driver supersedes all previous drivers. And it includes all their functionality plus much more.

I don't know what exactly encryption you have.
You can check it here: oledb-driver-for-sql-server-features


0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @nikz2246 ,

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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @nikz2246 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·