Loading a big file into a table in SQL Server
The differences between a slow load and a very fast load are:
- Flat file source: ValidateExternalMetadata = false
- OLE DB Destination: AccessMode: OpenRowSet using FastLoad (default is OpenRowSet)
Flat file: 230,000 rows, 15 columns, pipe separated, qualifier: double quote
SSIS: data conversion to Unicode on 10 columns
Big difference in performance:
- Before: 229 seconds (3.8 minutes)
- After: 3 seconds
So next time you load a file into a table using SSIS, please remember to use these settings.
Data Warehousing and Machine Learning 
Vincent:-
Do you think the entire file is checked when “ValidateExternalMetadata” is set to true.
Can you please provide numbers for when:-
1) ValidateExternalMetadata = true
2) OLE DB Destination: AccessMode: OpenRowSet using FastLoad
Thanks so very much,
Daniel
Comment by Daniel Adeniji — 21 August 2021 @ 12:12 am |
Hi Daniel, apologies I’m not able to provide you with those numbers. I wrote this article a long time ago and I haven’t used SSIS for a long time 🙂
Comment by Vincent Rainardi — 21 August 2021 @ 4:59 am |