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.
Leave a Reply