Today I sat in on a SSIS performance tuning “chalk talk” given by Ashvini Sharma at TechReady, an internal technical education event. Ashvini mentioned that when loading relatively small amounts of data into SQL (he characterized small as < 1 GB), you might be better served using “good enough” BCP.
Essentially, while the pipeline in SSIS is super-fast, it does do some up-front work before it starts copying rows that BCP does not. For any given dataset, BCP may therefore get a long enough head start at loading rows that SSIS and the pipeline can’t catch up. Interesting, but I think SSIS is so cool I’d probably use it anyway…I hate typing those god-awful BCP command-lines.