BCP vs DTS

Question

Post reply

BCP vs DTS

Carlos Yanes III

SSC Enthusiast

Points: 143
More actions
July 17, 2007 at 3:01 pm

#68769

To BCP or to DTS, THAT is the question. I find myself in a bit of a quandry over the relative benefits of the two and need some input from anyone who has ever had to deal with this choice. Any takers?
~Carlos

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ali Zaman Old Hand Points: 338 More actions · Answer 1

Hi Can Any one help,

I am new to this forum, so not sure if its the write place to post a question.

Well i got a flat file (in{CR}{LF} format) and needs to be imported into a sql server 2000.

now i need to know how can i create a package using DTS or some thing which kindoff breaks the data down and insert it into appropriate tables from the flat file. I need to find a way to carry it out in one process as the data is imported through the pipe. SO i have multiple tables which would eventaully contain the data from flat file after two weeks. is there any way i can automate the process.

regards

philcart SSC-Forever Points: 47796 More actions · Answer 2

Ali, I was going to say we'll forgive you for hijacking the topic, but you've done it four times. Start your own topic and you might get a better response.

Carlos, are you importing or exporting? Importing BULK INSERT is the fastest (there is a bulk insert task in DTS), exporting BCP is the fastest.

--------------------
Colt 45 - the original point and click interface

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Phill is spot on. I'll kick in that BCP has some additional features that make it a bit slower than Bulk Insert (but still faster than DTS) including error logging and rejection/isolation of bad rows in separate files. Both BCP and Bulk Insert can make use of a format file to resolve more complicated imports. BCP can also use a format file for exports. All of these things, BCP, Bulk Insert, and DTS can be scheduled to run using many of the different scheduling methods.

For sheer speed, Phill is absolutely correct... with or without a format file, Bulk Insert is the fastest. It's not uncommon to import 5 million 14 column rows in a minute or less.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ali Zaman Old Hand Points: 338 More actions · Answer 4

Sorry Guys for interuption. but as i said i am new to this forum.

So How can i start a new topic of my own.

i know it sounds quite disturbing but ill appreciate any help regards .

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Ali,

Simply go to the Data Transformation Services (DTS) forum (you can click on the link at the top of this page) and click the "New Thread" button at the top-left of the screen.

Greg

John Rowan SSC Guru Points: 56440 More actions · Answer 6

Ali,

Go to the forums main page: http://www.sqlservercentral.com/forums/default.aspx. Select the forum that you with to post in. You will then see a 'New Thread' button on the upper left of the forum.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Carlos Yanes III SSC Enthusiast Points: 143 More actions · Answer 7

Thanks Phil and Jeff. This definitely confirms what I was suspecting. Thanks guys!