Data Warehousing and Machine Learning

20 March 2012

SSIS: Loading a Big File Fast

Filed under: SSIS — Vincent Rainardi @ 7:21 am
Tags:

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.

2 Comments »

  1. 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 | Reply

    • 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: