Recently I posted SSIS 6 million rows in 60 seconds using standard laptop. Today I'm still using laptop and one physical disk but my 6 million rows load (heap table) is 30 seconds just by making several minor changes (and one bigger).
Below is the list and video that summarizes it:
1) Enterprise Database Engine is faster than BI Database Engine on my laptop (I really don't know why).
Rows Per Seconds: 113k
2) Commit Size = 0
Rows Per Second: 147k
3) Packet size = 32767
Rows Per Second: 155k
4) Compression = Row
Rows Per Second: 175k
5) Balance Data Distributor = 2 outputs (more than 2 is slower)
Rows Per Second: 204k
Some of you might be thinking it is fast because it is not first run and it cached data? Well after complete machine restart with best config it took 32.1 seconds.
To see more detailed videos see our SSIS Performance Tuning Heap Load Playlist
Katie and Emil