We’ve always (or most often than not) favor using the out-of-the-box functionality available with SSIS when dumping data from one location to another. Say for instance, a project that I recently worked on, had a requirement to dump out data from several tables to corresponding comma separated (CSV) files. The approach I took was to use a data flow task for each table, and simply added a data flow source for the SQL table (wrote a stored procedure to select out the data) and a data flow destination for the CSV, dragged the little green arrow, and the fields were automatically set (because the table and the CSV file had the same column names). Then repeated this for each table. Pretty and Simple no? Yes.
Dumping out the data of 5 very large tables however, took almost 35 minutes. Too much time. The whole thing had to run within half an hour according to the requirement.
Help came in the form of an ugly betty: Bulk Copy Program (BCP). The old, plain-looking yet trusted command line tool of SQL Server. The result: A whopping 29 minute reduction. The whole thing ran in just 6 minutes!
Ugly is indeed beautiful.
Data Maul 