Ugly is Beautiful: BCP in SSIS


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.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s