by Paul Bertucci
IN THIS CHAPTER
Bulk-Copy Program (BCP)
The Bulk-Copy Program
Logged and Non-Logged Operations
The BULK INSERT Statement (Transact-SQL)
Improving Load Performance
Data Transformation Services (DTS)
DTS Architecture and Concepts
Package Execution Utilities
Running the DTS Wizard
A Bit More on Metadata
The Bulk-Copy Program (BCP) is like an old friend to anyone who needs to get data in to (import) SQL Server tables from flat files or get data out of (export) SQL Server tables in to flat files. In fact, it is more like a warm security blanket because of the ease with which large amounts of data can be loaded or unloaded and the extreme reliability of that data movement. The good news with the SQL Server 2000 version of BCP is that it can handle most data types (including Image and Identity data types). In older versions of SQL Server, BCP was much more limited. And BCP can be extremely fast in its execution. This fact alone probably accounts for its continued presence in the MS SQL Server 2000 product.
Taking over the reigns for BCP and extending it into complex data transformations is Microsoft's Data Transformation Services (DTS). This chapter will describe the DTS environment and how DTS is addressing more complex data needs. The focus will be on importing, exporting, and transforming data from one or more datasources to one or more data targets. Other Microsoft solutions exist for importing and exporting data, but DTS can be used for a larger variety of data transformation purposes, and its strength is in direct data access and complex data transformation.
The alternatives to DTS and BCP in the Microsoft SQL Server 2000 environment include Replication, Distributed Queries, BULK INSERT, and SELECT INTO/INSERT. This chapter will help you determine how and when to use both BCP and DTS. And, more importantly, for what requirements.