CODEWITHSUNDEEP
ssisbids
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Fastest way to load flat file in ssis

I have a single flat file which needs to be loaded in a SQL Server. For that I have to use SSIS. Now I want to know about things which can help me loading these files in the fastest way:

  1. Should I use Flat file manager or Script task to load the flat file? (because in one of my question, i got an answer which states that script task load things faster)
  2. Destination (ADO.NET or SQL Server) ?
  3. Any other setting/Best Practice for flat file which I can make to load the file in faster way?

Upvotes: 2

Views: 3876

Answers (2)

William Salzman
William Salzman

Reputation: 6446

Here is the reference for how Microsoft was able to load 1TB in 30 minutes using SSIS.

I am surprised when you say that scripting is faster since to accomplish this same feat Microsoft used Flat File Sources and OLEDB destinations. They also optimized the load by breaking apart the load process into smaller chunks and partitioning the destination tables and by using very well tuned hardware. However, the techniques that they use in their SSIS packages are what I would use if I had to load a large dataset from SSIS.

(Microsoft) We Loaded 1TB in 30 Minutes with SSIS, and so can you

Upvotes: 2

Kris Gruttemeyer
Kris Gruttemeyer

Reputation: 872

I think for what you're trying to accomplish, SSIS would be a great way to go. It allows for much more flexibility. As far as using Flat File Manager/Scripting, scripting will always give you better performance but I use SSIS as it makes things easier to navigate (or repair). I'm sure many of the die hard SQL devs will tell you to script it but I find either way works.

As far as the destination, I exclusively use SQL Server so I can't speak to that part of your question.

Best practices are, in my opinion, keep it as simple as you can. The easier you make things the better performance you'll get. In my 3 years in SSIS, I always try to optimize ANY query to the best of my ability, THEN put it into SSIS.

It sounds like you're doing nothing more than a simple ETL on these files, if that's the case, I recommend SSIS based on my experience. Once you have everything loaded you can modify the data types for your different cases of char, varchar and int.

Hope this helps!

Upvotes: 1

Related Questions