We help IT Professionals succeed at work.

bcp Vs DTS

Andrew Crofts
on
Is there any good reason for using bcp rather than DTS under MSSQL 7 or 2000?

If you wished to invoke the process from a VB application, would this make any difference?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
both DTS and BCP will run on the server, thus i would not make my choice by the fact that it will be started by VB application. i would create a job that does all the steps, this will reduce the work in VB to a single SQL:
exec sp_startjob "jobname"

If you want to load flat files, i would go with BCP, but the important point is not if you use BCP or DTS, but that you load the file(s) to staging tables first, where you can analyse them first for consistency, and dont't disturb the production tables as long as data has not been validated.

Cheers

Author

Commented:
>If you want to load flat files, i would go with BCP,

Any particular reason?

Author

Commented:
>exec sp_startjob "jobname"

would sp_startjob be a user defined sp? Can you just put a bcp command in a sp or do you use something like sp_executesql?
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
BCP is designed to work with flat files, while DTS can work with "any" database. using BCP, you have a much more mature tool, and clearer error messages than with DTS.

sp_startjob is a stored procedure that exists in the MSDB database. the job(s) i am speaking of can be managed in the Enterprise Manager, Management, SQL Server Agent, Jobs
One job can be defined by several steps, each step could be 1 (or more) TSQL statement(s) or starting executables (like BCP) ...
Thus, your application would just start this job, and in case the job must be modified, you don't need to recompile the application...

Cheers

Author

Commented:
Thanks. Very useful.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.