SSIS: Loading a Big File Fast

Loading a big file into a table in SQL Server

The differences between a slow load and a very fast load are:

1. Flat file source: ValidateExternalMetadata = false
2. 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.