Importing and Exporting Data with DTS and BCP

Article
01/28/2010

Updated : July 19, 2001

In the old days, bulk copy program (BCP) was just about the only tool Microsoft SQL Server administrators could use to move data around. BCP is extremely fast and has a minimal overhead, but it also has a very rigid (and sometimes unforgiving) syntax. Then along came Data Transformation Services (DTS), an improved method for importing and exporting data between heterogeneous data sources. Whether you want to move data from a legacy system on a onetime basis or continually move data back and forth for data warehousing, DTS should be your first choice. With DTS you don't need to struggle with BCP anymore. DTS is extremely flexible and surprisingly fast, and you can use the technology to copy and transform data to or from any OLE DB or ODBC data source.

Understanding DTS

DTS is designed to move data accurately and efficiently as well as to convert or transform data between heterogeneous data sources. You can use DTS when you want to

Move data between heterogeneous systems, such as from Oracle to SQL Server or vice versa
Move data between SQL Servers, including primary and foreign keys
Move data from Microsoft Access or Microsoft Excel to SQL Server or vice versa
Extract data; transform the data by performing column mappings, filling in missing values, and so on; and then import the data on the destination system
Copy views from one database to another

Tip The DTS Import/Export Wizard can run the import/export process between any of the available data sources. You don't have to set SQL Server as either the source or the destination. For example, you can use the DTS Import/Export Wizard to copy data from a text file to an Excel spreadsheet.

The key components of DTS are database drivers, packages, and other support tools.

DTS and Database Drivers

OLE DB and ODBC drivers are a key part of DTS. Without these drivers, you couldn't communicate with other systems. SQL Server provides native OLE DB and ODBC drivers for

SQL Server
Oracle
Microsoft OLAP Services
Microsoft Internet Publishing
Access and Excel
ASCII text files
Other ODBC data sources

The text file driver is the catchall for import and export procedures. If you don't have a native driver for your legacy database and you can't use the generic ODBC driver, you can usually export the data to a text file and then import it into SQL Server. You can go from SQL Server to a legacy system using the same technique.

DTS Packages

At the heart of DTS you'll find the DTS Import/Export Wizard. You use the DTS Import/Export Wizard to create DTS packages, which you can later view or modify using DTS Designer. Packages are simply sets of tasks for importing, transforming, and exporting data that you can reuse or schedule to run as often as needed. Packages can be

Stored in the repository database on a local or remote server
Shared through SQL Server Meta Data Services
Stored outside the database in COM-structured storage files, which is useful when you want to copy, move, and e-mail packages to another location
Stored in Visual Basic files where you can use them in Visual Basic programs.

You execute packages directly from Enterprise Manager using the DTS Designer, DTS Import/Export Wizard, or from the Data Transformation Services section. You can also execute packages from the command prompt using the Dtswiz command-line utility, the DTS Run utility for Windows, or the Dtsrun command-line utility. Within DTS packages, you'll find

Connections Store information about the source or destination of data. In a connection, you specify the data provider to use (such as the Microsoft OLE DB Data Provider for SQL Server), the server to which you want to connect, the logon to use for the connection, and the database to work with for the import/export operation. In DTS Designer, you select connections using the Data menu.
Tasks Set the operations that need to be performed within the package. Tasks can consist of ActiveX scripts, SQL scripts, SQL queries, commands to transfer SQL Server objects, data-driven queries, bulk insert commands, and external processes to execute. You can even have DTS send e-mail when a package completes.
Workflow procedures Set when and how a particular task should be executed, such as on completion, on failure, or on success. For example, you could schedule a task that sends e-mail on failure or on success.
Data transformation procedures Set the step-by-step transformation process for the data. Also referred to as Data Pumps.

You can store a DTS package on any SQL Server and you don't need to create or store it on the source or destination server associated with the package. If you're editing, modifying, scheduling, or just viewing a DTS package, you need to use the user account of the package owner or an account that operates under the Sysadmin role on the SQL Server where the package is actually stored.

You can set general properties for all packages created on a particular SQL Server database instance using DTS application options. To set DTS application options, complete the following steps:

Start Enterprise Manager and then access the server instance you want to use.
Right-click the Database Transformation Services folder and then select Properties.

You can now set the following general options for packages:
- Cache Enables caching of DTS application environment. Caching decreases the time needed to open packages in DTS Designer, but you must remember to refresh cache when you change the DTS environment by adding new scripting languages, custom transformations, OLE DB providers, or custom tasks. Only applies to SQL Server 2000 on Microsoft Windows 2000.
- Refresh Cache Clears and refreshes the DTS Cache. When caching is enabled, you must refresh when you change the DTS application environment. You should refresh after you add a new installation of SQL Server 2000 to a server, register any new objects or tasks in DTS packages, or add a new OLE DB provider.
- Show Multi-Phase Pump In DTS Designer Displays the multi-phase data pump options in any DTS package that has transformation tasks. You can access multi-phase data pump options when you're configuring transformations in either the Data Driven Query Task or Transform Data Task.
- Turn On Just-In-Time Debugging Allows DTS Designer to use the Visual InterDev script debugger to debug Microsoft ActiveX scripts. Only applies if you are running Microsoft Visual InterDev or have installed the Microsoft Windows NT 4.0 Option Pack.

Other DTS Tools

Other tools available for DTS are

DTS Designer A tool for developing and maintaining packages. With DTS Designer you have complete control over every step of the transformation process. After you create a package in the DTS Import/Export Wizard, you can use DTS Designer to modify the package and to set additional tasks, workflow, and other procedures.
DTS COM objects Extensible components for integrating DTS functionality into external applications and scripts. A powerful scripting utility is integrated into DTS Designer.

Creating Packages with the DTS Import/Export Wizard

Creating a DTS package is one of the most complex tasks you'll perform as a database administrator. Although the DTS Import/Export Wizard, fortunately, is designed to help you build DTS packages without a lot of fuss, the process is still involved. To help reduce complexity, I'll divide the creation process into stages and then examine each stage individually. The stages you use to create DTS packages are

Stage 1: Source and Destination Configuration
Stage 2: Copy, Query, or Transfer
Stage 3: Formatting and Transformation
Stage 4: Save, Schedule, and Replicate

To get started with DTS, start the DTS Import/Export Wizard and then click Next to advance to the Source selection page. You start the DTS Import/Export Wizard in Enterprise Manager by completing the following steps:

Start Enterprise Manager and then access the server instance you want to work with.
Right-click the Database Transformation Services folder and then select All Tasks. Then select either Import Data or Export Data.

You don't need to start Enterprise Manager to run the DTS Import/Export Wizard. Instead, you can start the DTS Import/Export Wizard from the menu by completing the following steps:

Access the Microsoft SQL Server menu by selecting Start and then Programs.
Select Import And Export Data.

You can also run the DTS Import/Export Wizard from the command line by entering dtswiz.

If necessary, you can specify setup options through the command line as well. You can use these options to preconfigure information needed for the source or for the destination connection (but not both). The Dtswiz options are shown in Sample 7-1, and they are defined in Table 7-1. As shown in the usage example, you configure source information using the /i switch and then enter additional options. You configure the destination information using the /x switch and then enter additional options.

Sample 7-1 Dtswiz Syntax and Usage

Syntax

dtswiz [{/i or /x}] [/s<servername\instancename>] [/u<username>]    [/p<password>]
      [/d<database>] [/m] [/y] [/n] [/?]

Usage

dtswiz /i /szeta /usa /pgorilla /dcustomer
dtswiz /x /somega /usa /pgorilla /dcustomer2

Table 7-1 Dtswiz Switches Defined

Switch	Definition
/I	Sets import operation and configures source with other options.
/x	Sets export operation and configures destination with other options.
/s<servername>	Sets server name.
/u<username>	Sets user name for SQL Login id.
/p<password>	Sets password for SQL Login id.
/d<database>	Sets database.
/m	Sets the execution on main thread property for the package.
/y	Hides system databases.
/n	Sets Microsoft Windows authentication. Windows Authentication has precedence over the SQL Logon ID and password.
/?	Displays syntax and usage help.

Stage 1: Source and Destination Configuration

In Stage 1 you choose the source and destination for the import/export operation. If you started a DTS Import/Export Wizard and clicked Next, you should be on the Choose A Data Source page. At this stage you should complete the following steps:

Use the Source drop-down list box to select the source for the import/export operation. SQL Server provides native drivers for OLE DB and ODBC data sources. Select the data source that matches the type of file, application, or database you want to use as the source. For example, if you were copying from an Excel 2000 spreadsheet, you would choose Microsoft Excel 97-2000.
Fill in any additional information required to establish a connection to the source. The source you select determines what additional information you need to supply. Click Next.
Use the Destination drop-down list box to select the destination for the import/export operation.
Fill in any additional information required to establish a connection to the destination. As with the source, the destination you select determines what additional information you need to supply.
Click Next to go on to Stage 2: Copy, Query, or Transfer.

If choosing a source and destination were that easy, it would be a snap. But sometimes it isn't clear what additional information you need to provide. Basically, this is because there are several types of sources and destinations that you can select. These sources include

File-based data connections
DSN connections through ODBC drivers
Server-based connections to databases other than SQL Server
Server-based connections to SQL Server
Text files

The sections that follow take a closer look at each of these connection categories.

File-Based Data Connections

You use file-based data connections with applications and databases that are file-based. You'll use this type of connection with dBase, Access, Excel, and Paradox. With file-based connections, you use a dialog box similar to the one shown in Figure 7-1. You need to enter the following information:

File Name The full file or Uniform Naming Convention (UNC) path to the source or destination file, such as //omega/data/excel/cust.xls
Username A valid username for accessing the source or destination file
Password A valid password for accessing the source or destination file

Figure 7-1: With file-based connections, you need to specify the full path to the source/destination file and give authentication information, if necessary.

DSN Connections Through ODBC Drivers

Data source name (DSN) connections are used with most databases that use ODBC drivers. You'll use this type of connection with dBase VFP, Microsoft Fox Pro, Microsoft Visual Fox Pro, Oracle, and other ODBC data sources. If you're setting up an ODBC connection to an Oracle database, you'll need the client connectivity tools or a third-party ODBC driver installed on the server you're establishing a connection from. Further, it's usually much easier to use the Oracle ODBC client driver with an associated system DSN that can use an Oracle TNSNAMES.ORA configuration file. The TNSNAMES.ORA file should list all the current Oracle DB server host names, IP addresses, and the associated listening ports that are set up for all Oracle DB instances. With DSN connections, you use a dialog box similar to the one shown in Figure 7-2. You configure the DSN connection by completing the following steps:

With user or system DSNs, select the User/System DSN option button and then use the drop-down list box to select any of the available DSNs. User and system DSNs must be on the local system.
With file DSNs, select the File DSN option button and then enter the full path to the DSN. File DSNs can be located anywhere on the network.
If you need to create a new user, system, or file DSN, click New and then use the Create New Data Source Wizard to create a new DSN. Afterward you may need to enter additional setup options.

Figure 7-2: With DSNs, you need to specify the type and name of the DSN to use and you need to supply authentication information.
Type a username and password that can be used to access the data source.
Click Advanced to set advanced options for the driver/provider you're working with.

Server-Based Connections to Databases Other than SQL Server

You use server-based data connections to connect to databases other than SQL Server. You'll use this type of connection with Oracle, Microsoft OLE DB Provider for Internet Publishing, and Microsoft OLE DB Provider for OLAP Services. You configure server-based connections by setting Data Link properties that connect to a data source. Data Link Properties have four components:

An OLE DB provider, which you can select using the Source or Destination selection list in the DTS Import/Export Wizard or through the Provider tab in the Data Link Properties dialog box.
Connection options, which you set using the Connection tab in the Data Link Properties dialog box. Connection options typically include a data source name or connection string accompanied by the user name and password information needed to log on to the database.
Advanced options, which you set using the Advanced tab in the Data Link Properties dialog box. Advanced options let you configure network settings, timeouts, and access permissions (as long as these options are configurable).
Initialization properties, which you view using the All tab in the Data Link Properties dialog box. The initialization properties display all the options you've configured for the provider and provide a central location for editing values. Simply double-click a value to edit the associated settings.

With Oracle, the Oracle client and networking components must be installed on the system running SQL Server. If these components aren't installed, you won't be able to use the OLE DB provider. Assuming the Oracle client is installed on your system, you could set the Data Link properties for Oracle by completing the following steps:

In the DTS Import/Export Wizard, select Microsoft OLE DB Provider for Oracle on the Source or Destination selection list. Click Properties. This displays the Data Link Properties dialog box with the Connection tab selected.
As shown in Figure 7-3, type the name of the Oracle server to which you want to connect. Afterward, type the username and password needed to log on to the database.
To test the connection to the server, click Test Connection. If the connection fails, you may have improperly configured the Oracle client.
You can use the Advanced and All tabs to view additional options. Change these options as necessary.
Click OK.

Figure 7-3: To configure data transformation with an Oracle server you'll need to install the Oracle client software and establish a connection to the server using Data Link.

Server-Based Connections to SQL Server

You can connect to SQL Server using an ODBC driver or an OLE DB provider. Either way, the options you have are the same as those shown in Figure 7-4, and you can configure the connection by completing the following steps:

Use the Server drop-down list box to select the SQL Server for the connection. The (local) option lets you choose the local system as the source or destination. Because remote procedure calls (RPCs) are used to determine available servers, the server you want to use may not be listed. In that case, click Refresh or just type in the server name.
Next, select an authentication method. Type a username and password, if necessary.
Use the Database drop-down list box to select a database to work with.
Click Advanced to set advanced options for the driver/provider you're working with.

Figure 7-4: Connecting to SQL Server is different from other types of connections.

Importing and Exporting Text Files

You can use text files as a data source or destination. When you do, you must provide additional information about the input or output formatting. In either case, the steps are similar. To use text files as a data source, use the process below as an example and complete the following steps:

From the DTS Import/Export Wizard, choose the Text File option. Then enter the full filename or UNC path to the file you want to work with.

Tip If the file is in use, you'll get an error message. Click OK and then select the file again. (This forces the DTS Import/Export Wizard to try to read the file again. Otherwise, you won't see the results of the formatting.)
After you enter the text file information, click Next. You'll see the dialog box shown in Figure 7-5.
Specify how the file is delimited. If the file has fixed-width columns, select the Fixed Field option button. If the columns are delimited with commas, tabs, semicolons, or other unique characters, select the Delimited option button.
Select the file type using the File Type drop-down list box. The file must be formatted as ANSI (ASCII text), OEM (original equipment manufacturer), or Unicode. For importing, OEM normally refers to the native SQL Server format.

Specify the end-of-row delimiter using the Row Delimiter drop-down list box. The available options are
- {CR} {LF} for carriage return and line feed
- {CR} for carriage return only
- {LF} for line feed only
- Semicolon
- Comma
- Tab
- Vertical bar for the | character
  
  Figure 7-5: After you enter the file information, you need to specify the file format.
Use the Text Qualifier drop-down list box to specify the qualifier for text as Double Quote ("), Single Quote ('), or None.
If the first row contains column headers, select First Row Has Column Names.

Tip Column headers make it easier to import data. If the file doesn't contain column names, you may want to click Cancel, add the column names to the first line, and then restart the import/export procedure.
To skip rows at the beginning of a file, use the Skip Rows field to set the number of rows to skip.

Note: If you indicated that the first row contains column names, the first row is read and then the specified number of rows is skipped.
Click Next.
If the file is delimited, the next screen allows you to select the delimiter as a comma, a tab, a semicolon, or another character. Use the option buttons provided to make your selection. If you choose Other, enter the characters that form the delimiter as well. As shown in Figure 7-6, the Preview area shows the data format for the options you've chosen in this dialog box and the previous dialog box.

Figure 7-6: Select a delimiter and then double-check the formatting of the file. If something is wrong, make changes before you proceed to the next stage.

Tip If you notice data elements out of place, you can click Back to reconfigure the previous dialog box. You may also need to modify the source file. In this case, click Cancel, modify the file, and then restart the DTS Import/Export Wizard.
If you selected fixed-width, you'll need to tell the DTS Import/Export Wizard where columns start and end. Vertical lines indicate the start and end of columns. Add column markers by clicking in the Preview area to create a column marker. Remove column markers by double-clicking them. Move column markers by clicking them and dragging them to a new position.
Click Next when you're ready to select the destination for the import/export operation. After selecting the destination, you'll go on to Stage 2.

Stage 2: Copy, Query, or Transfer

With most import or export procedures, the next stage involves specifying tables and views to copy, building a query, or selecting objects to transfer. You'll select the operation using the dialog box shown in Figure 7-7, and then you'll proceed as described in the sections that follow.

Figure 7-7: Determine whether to copy tables and views, build a query, or transfer objects.

Specifying Tables and Views to Copy

If you want to copy tables and views to the destination, you'll need to select which tables and views you want to copy. When a text file is the data source, making the selection is easy—there's only one table available and you can't select any

views. With other data sources, you'll need to select the tables and views you want to work with. In either case the dialog box you use is the one shown in Figure 7-8.

When selecting tables, complete the following steps:

In the Specify Table Copy Or Query dialog box, select the Copy Table(s) And View(s) From The Source Database option button and then click Next.
Select a table by clicking its entry and then preview the data the table contains by clicking Preview.
When you find a table you want to copy, place a check in the related Source Table column.
By default, the destination name of the table is set to be the same as the source table name. If you want to change the table name, edit the corresponding value in the Destination Table column.
If you want to manipulate the row values in a table, select it and then click the corresponding button in the Transform column. Transforming row values is covered in Stage 3.

Figure 7-8: Use the Select Source Tables And Views dialog box to specify which tables you want to copy.

Building a Query

Another way to select data for exporting is to build a query and execute it against the source file, spreadsheet, or database. Regardless of the type of data source, you build the query in the same way by completing the following steps:

In the Specify Table Copy Or Query dialog box, select the Use A Query To Specify The Data To Transfer option button and then click Next.

In the Type SQL Statement query dialog box you can
- Type a query directly in the text box provided and then parse it to check for accuracy using the Parse button
- Click Query Builder to build a query using the Query Builder utility
Tip You could also create a query in your favorite query builder and then paste the results into the Type SQL Query dialog box.
Query Builder provides the easiest alternative, so click Query Builder. This displays the Select Columns dialog box shown in Figure 7-9.

Select columns for the query using the following techniques:
- Select all the columns in a table Click the table name in the Source Tables list box and then choose the add button (>).
- Add individual columns Click the plus sign (+) next to a table name in the Source Tables list box. This displays the columns in the table. Now select a column by clicking it and then choose the add button (>).
- Remove a column from the Selected Columns list box Click it and then choose the remove button (<).
- Remove all selected columns from the Selected Columns list box Click the remove all button (<<).
  
  Figure 7-9: In the Select Columns dialog box, select the columns you want to query.
- Change the order of selected columns Click the column you want to reorder and then use the Move Up or Move Down button to change the column order.
Click Next when you're ready to continue. You can now select the sort order for columns using the dialog box shown in Figure 7-10. The buttons provided are used as follows:
- > Add the selected column
- >> Add all remaining columns using their current order
- < Remove a column from the Sorting Order list box
- << Remove all columns from the Sorting Order list box
- Move Up Change the order of a selected column by moving it up the list
- Move Down Change the order of a selected column by moving it down the list
Figure 7-10: Use the Specify Sort Order dialog box to set the sort order for the data.
Set the query criteria. To choose all rows in the selection, select the All Rows option button. To set specific query criteria, select the Only Rows Meeting Criteria option button and then use the fields provided to set the query criteria. Figure 7-11 shows an example.

Figure 7-11: Select the columns you want to work with and the values to search for. Use the And/Or operation to determine how to apply multiple query parameters. Use the build button (…) to the right of the query criteria to see the available values for the selected column.
The result of the Query Builder procedure is a complete SQL statement that you can use to select data for exporting.
Click Parse to ensure that the query runs. If necessary, rebuild the query or remove statements that are causing errors.
Click Next to continue. The next dialog box you'll see is the Select Source Table(s) And View(s) dialog box discussed in the section of this chapter entitled "Specifying Tables and Views to Copy."
The default table name is Results. Click in the Destination Table column to set a new name for the destination table.
Click the button in the Transform column to manipulate the data before writing it to the destination. Transforming the data is covered in Stage 3.

Selecting Objects to Transfer

The only time you can select objects to transfer is when you copy between SQL Server databases. You select objects to transfer by completing the following steps:

In the Specify Table Copy Or Query dialog box, select the Copy Objects And Data Between SQL Server Databases option button and then click Next. This displays the Select Objects To Copy dialog box shown in Figure 7-12.

Figure 7-12: In the Select Objects To Copy dialog box, select the objects you want to transfer.

You use the options of the Select Objects To Copy dialog box as follows:
- Create Destination Objects When selected, creates objects at the destination (if necessary) and allows you to determine what happens to existing objects as well as whether dependent objects are transferred.
- Drop Destination Objects First Drops existing objects from the destination database.
- Include All Dependent Objects Ensures that dependent objects are transferred even if they aren't explicitly selected.
- Include Extended Properties Ensures extended properties are transferred even if they aren't explicitly selected.
- Copy Data When selected, copies data along with the source objects. When cleared, only the objects are created at the destination.
- Replace Existing Data If you choose to copy data, you can replace all existing data for the selected objects by selecting this option button.
- Append Data If you choose to copy data, you can add data from the source objects to the current data in the destination database by selecting this option.
- Use Collation When selected, the current collation is preserved in the destination database. Otherwise, no collation is used and the objects aren't set to a specific collation. As a result, the destination database's default collation takes effect.
- Copy All Objects When selected, transfers all objects from the source to the destination. Cancel this option and then click Select Objects to select individual objects.
- Use Default Options Select this option to use the default transfer options. To configure the options manually, clear this check box and then click Options.
- Script File Directory Sets the directory for scripts created when you select the Generate Scripts In Unicode option in the Advanced Options dialog box.
Click Next to continue to Stage 4, skipping Stage 3.

Stage 3: Formatting and Transformation

Transformation is the process of manipulating the source data and formatting it for the chosen destination. The way you transform and format data depends on the destination you've chosen. With most types of files, databases, and spreadsheets, you go through a column mapping and transformation process. But if you've chosen a text file as the destination, you must also specify the format of the output file. Because the formatting options are essentially the same as those used for importing, I won't cover them here and will instead refer you to the "Importing and Exporting Text Files" section of this chapter.

Unless you specify otherwise, the DTS Import/Export Wizard sets default mapping for all selected tables. This default mapping

Specifies that every column in the source table is copied
Maps the original column name, data type, nullability, size, precision, and scale to the destination table
Appends the source data to the destination table and creates the destination table if it doesn't exist

You can override the default mappings by completing the following steps:

The Select Source Tables dialog box lists the results of your query or all of the available tables in the source database, spreadsheet, or file that you've selected. If you've selected a particular table, you'll find a button in the Transform column. Click this button to open the Column Mappings And Transformations dialog box shown in Figure 7-13.

Click the Column Mappings tab and then set the general transfer options as follows:
- Create Destination Table Creates the destination table before copying source data. If the destination table exists, you must select Drop And Recreate Destination Table. Otherwise an error will occur.
- Delete Rows In Destination Table Deletes all rows in the destination table before copying the source data. Indexes and constraints on the destination table remain.
  
  Figure 7-13: Use the Column Mappings tab to map individual values in the selected table's columns.
- Append Rows To Destination Table Inserts the source data into the destination table instead of overwriting existing data. This option doesn't affect existing data, indexes, or constraints in the destination table.
  
  Note: Rows may not necessarily be appended to the end of the destination table. To determine where rows are inserted, use a clustered index on the destination table.
- Drop And Recreate Destination Table Drops and recreates the destination table before attempting to copy data into it, which permanently deletes all existing data and indexes.
  
  Tip If the table exists at the destination, you must drop and recreate it to map new column values to the destination table. Otherwise, you can only map source columns to different destination columns.
- Enable Identity Insert Allows you to insert explicit values into the identity column of a table. This option is available only on SQL Server and only if an identity column is detected.
- Edit SQL Displays the Create Table SQL Statement dialog box, which allows you to customize the default CREATE TABLE statement.
After you set the general transfer options, use the fields in the Mappings list box to determine how values are mapped from the source to the destination. The fields of this list box are all set to default values based on the source column. If you want to override these values for a new table or if you're dropping and recreating an existing table, you can modify these values. The Mapping fields are used as follows:
- Source Sets the source column to map to a destination column. If you choose <ignore>, the source data isn't copied. Entering <ignore> can result in an error if there is no DEFAULT value and the destination is defined as NOT NULL.
- Destination Click in this column and then select an existing column name or type a new column name for the destination table. Use the <ignore> option if the destination column shouldn't be created.
  
  Note: If the destination column already exists and you choose <ignore>, the source data isn't copied into this column.
- Type Select a data type for the Destination column. If you select a different data type than the source column, the data is converted to the new data type during the transfer.
  
  Note: Make sure you select a valid conversion option. The DTS Import/Export Wizard won't let you truncate data, and if you try to, an error occurs.
- Nullable Select this check box if the destination allows NULL values.
- Size Sets the length of the destination column. This value is applicable only for the char, varchar, nchar, nvarchar, binary, and varbinary data types.
  
  Note: Setting the size smaller than the length of the source data can result in data truncation. If this happens, the DTS Import/Export Wizard will generate an error and won't complete the transfer.
- Precision Sets the maximum number of decimal digits, including decimal places. For decimal and numeric data types only.
- Scale Sets the maximum number of digits to the right of the decimal point. This value must be less than or equal to Precision and applies to decimal and numeric data types only.
Once you complete the mappings, you can fine tune the transformation. Click the Transformation tab. As shown in Figure 7-14, the DTS Import/Export Wizard now lets you perform the actions on the following page.
- Copy source columns directly to destination columns and set advanced flags for the transformation.
- Transform information as it's copied to the destination using a script.
Figure 7-14: With the Transformations tab you can set transformation options or use a script for the transformation.
To copy source columns directly, select the Copy The Source Columns Directly To The Destination Columns option button. By default, all possible conversions are allowed.
To use a script for the transformation, select Transform Information As It Is Copied To The Destination. Next, use the Language drop-down list box to select your preferred scripting language. When you make a selection, the default script is converted to your chosen scripting language. You can then add to the default script as necessary or click Browse to find and import a script you've created in another application.
Click OK and then repeat this process for other tables you want to transform.
When you're ready to continue, click Next.

Stage 4: Save, Schedule, and Replicate

The end of the road is in sight, really! At this stage you specify when to use the package you've created and whether the package should be saved for future use. Now you should be on the Save, Schedule, And Replicate Package dialog box shown in Figure 7-15. To use this dialog box, complete the following steps:

Use the options under the When heading to specify when the DTS package is executed. You can use any combination of the available options, including
- Run Immediately Run the package now
- Use Replication To Publish Destination Data Set up replication between the source and destination using the package you just created
- Schedule DTS Package For Later Execution Set up a schedule for when this package should run
Tip You don't have to select any of these options to save the package. Simply clear all the check boxes under the When heading and then select a Save DTS Package option.
Use the options under the Save heading to save the package for future use. If you want to save the package for use later, you should select the Save DTS Package check box and then specify where the package should be saved. The available locations are
- SQL ServerSaves as a local package where the package is accessible for use on the designated server.
- SQL Server Meta Data Services Saves to the designated server's repository database, where the package can be shared with other servers through Meta Data Services.
  
  Figure 7-15: Now that you've configured the package, you can use the Save, Schedule, And Replicate Package dialog box to run it and save it for future use.
- Structure Storage File Saves as a COM-structured file. You can add additional packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
- Visual Basic File Saves as a Visual Basic file, where the package can be used in Visual Basic programs.
When you're finished configuring run and save options, click Next.
If you've opted to save the package, the next dialog box lets you set the save location (see Figure 7-16). The options are slightly different depending on the save location you previously selected.
Type a name and description of the package using the Name and Description fields, respectively. The name should be unique for the target location.
If you're saving the package to SQL Server you can password-protect the package and prevent unauthorized users from working with it. Type an owner password and a user password in the fields provided. Anyone with the owner password can design, schedule, and execute the package. Anyone with the user password can only schedule or execute the package. Use the Server Name drop-down menu to select which SQL Server the package is saved to. Select the type of authentication to use by selecting one of the option buttons for either Windows Authentication or SQL Server Authentication. Provide an authorized username and password if you select SQL Server Authentication.

Figure 7-16: In the Save DTS Package dialog box, provide additional information needed to save the package to a local or remote system.
If you want to save the package to either a Structured Storage or Visual Basic file, set the file location using the File Name field. If you choose the SQL Server Meta Data Services option, select a destination server to save the package to by using the Server Name drop-down list box or type the server name directly. Select an authentication mode and type a username and password as needed.
Click Next and then Finish. If you've elected to run the package immediately, SQL Server runs the package. As each step is completed (or fails), the status is updated. If an error occurs, you can double-click its entry to view a detailed description of the error. Errors may halt execution of the package, and if they do, you'll need to redesign the package using DTS Designer or recreate the package using the DTS Import/Export Wizard.

Working with DTS Packages

SQL Server stores DTS packages as local packages, Meta Data Services packages, and file-based packages. You manage packages through Enterprise Manager, the DTS Run utility for Windows, or through the Dtsrun command-line utility.

Examining, Running, and Scheduling Packages

In Enterprise Manager you can examine, run, or schedule packages you've created by completing the following steps:

Start Enterprise Manager and then access the server you want to work with.
Click the plus sign (+) next to the server's Data Transformation Services folder.

You can now perform the following tasks:
- View local packages To view local packages on the selected server, click the Local Packages entry in the left pane. As shown in Figure 7-17, local packages are then listed in the right pane.
- View Meta Data Services packages To view packages in the server's repository database, click the Meta Data Services Packages entry in the left pane.
- View meta data To access the repository database's meta data, click the Meta Data entry in the left pane.
- Run a package To run a local or repository package, right-click its entry in the right pane and choose Execute Package from the shortcut menu.
- Schedule a package to run To schedule a local or repository package, right-click it and from the shortcut menu, choose Schedule Package. Then configure the package as a recurring job using the Edit Recurring Job dialog box. Scheduling is covered in Chapter 12, "Database Automation and Maintenance."
  
  Figure 7-17: You access packages through the Data Transformation Services folder.
- Modify a package's design To modify a package's design, right-click it and from the shortcut menu, choose Design Package. This starts the DTS Designer.
- View or modify file packages To view or modify packages stored in files, right-click the Data Transformation Services folder, choose All Tasks from the shortcut menu, and then click Open Package. Use the Select File dialog box to select the package.

Managing Packages Using the DTS Run Utility for Windows

You can use the DTS Run utility for Windows to execute and manage DTS packages as an alternative to the Dtsrun command-line utility. The DTS Run utility for Windows provides a graphical interface to most of the execution options that are available with the Dtsrun command-line utility. You start the DTS Run utility for Windows by completing the following steps:

From the command line, type dtsrunui. This starts the DTS Run utility and displays the DTS Run dialog box as shown in Figure 7-18.
Use the Location drop-down list box to specify the type of package you'll be working with. Select either SQL Server, SQL Server Meta Data Services, or Structured Storage File.

Figure 7-18: The DTS Run utility for Windows provides a subset of the available execution options in the Dtsrun command-line utility in the form of dialog boxes.
Use the Server combo box to choose an existing source server location or to specify the source server location. Afterward, choose the authentication type. With SQL Server authentication you must also provide a username and password.
Click the build button (…) next to the Package Name field. You'll see the Select Package dialog box as shown in Figure 7-19.

Figure 7-19: The Select Package dialog box provides a list of available packages and specific versions associated with each package.
Click the + sign next to the package that you want to execute. This will display specific versions associated with the package as shown in Figure 7-19. Select the version of the package that you want to execute and then click OK.
In the DTS Run dialog box, click Advanced to set additional options for global variables and logging. You can also use the Generate button to create a command-line script that executes the package.
To schedule the package to run at a later time, click Schedule. Specify the run schedule for the package and then click OK.
After you complete all modifications, click Run to run the package. The Executing DTS Package dialog box indicates the status of the package execution process. A message is displayed when the package has completed executing. Click OK to close the message dialog box. Click Done to close the Executing DTS Package dialog box.

Managing Packages from the Command Line

To manage packages from the command line, you can use Dtsrun. This utility works with all package types. By default, Dtsrun executes the specified package, but you can also use the utility to delete or overwrite packages. You delete local or repository packages to drop them from SQL Server. You overwrite file-based packages to empty their associated file. The syntax and usage for Dtsrun is shown as Sample 7-2.

Sample 7-2 Dtsrun Syntax and Usage

Syntax

dtsrun /~S ServerName [{[/~U UserName /~P Password] or [/E]}]
   /~N PackageName [/~M PackagePassword] [/~G PackageGuidString]
   [/~V PackageVersionGuidString] [/~A GlobalVariableName]
   [/~L LogFileName] [/~W WriteStatusToEventLogTrueOrFalse ]
   [/~F StructuredStorageUNCfilename] <overwritten if /S also       used>
   [/~R RepositoryDatabaseName] <load package from repository if       blank>

By default, Dtsrun executes the package. You can change this behavior with these options:

/!X <Do not execute; retrieve Package to /F filename>
/!D <Do not execute; drop package from SQL Server>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)>

Usage

dtsrun /~S"zeta" /~U"sa" /~P"gorilla" /~N"DTS Customer"

Note: The tilde (~) is optional. If you use the tilde, the parameter is specified as hexadecimal text with an encrypted value.

Understanding BCP

BCP offers a command-line alternative to DTS. The Transact-SQL counterpart to BCP import is BULK INSERT. You'll find that BULK INSERT has a similar syntax when used for importing data. To learn more about BCP, let's examine

Basics
Syntax
Permissions
Modes
Importing data
Exporting data

BCP Basics

Although BCP has been around for some time, it may remain a favorite of database administrators for a while longer because of its great performance and minimal overhead. You'll find that import and export processes tend to be very fast and that BCP uses very little memory to operate. BCP doesn't have a graphical user interface (GUI) and is best used in two situations:

To import data from a text file to a single SQL Server table or view
To export data to a text file from a single SQL Server table or view

When transferring data to or from SQL Server, BCP uses ODBC. This is a change from previous versions that communicated with SQL Server through the DB- Library.

Tip With ODBC datetime, smalldatetime and money data types are treated differently than with DB-Library. You'll find that the datetime format is now yyyymmdd hh:mm:ss rather than mmm dd yyy hh:mm (A.M./ P.M.) and the money format now has no commas with four digits after the decimal instead of commas and two digits after the decimal.

Note: When importing with BCP, columns with computed values and timestamps are ignored. SQL Server can automatically assign values. To do this, use a format file to specify that the computed values or timestamp columns in the table should be skipped; SQL Server then automatically assigns values for the column. During export, computed values and timestamps are handled like other values.

BCP Syntax

Before exploring how to use BCP, let's look at the command syntax, which, as you can see from Sample 7-3, is fairly extensive. BCP switches are case and order sensitive. You must use them in the exact manner expected. If you don't, you'll have problems.

Table 7-2 provides a summary of key BCP parameters.

Sample 7-3 BCP Syntax and Usage

Syntax

bcp {[[dbname.][owner].]{tablename | viewname } | "query"}
   {in | out | queryout | format} datafile
   [switch1 [parameter1]] [switch2 [parameter2]]
   [switchN [parameterN]]

Usage

bcp pubs..customer out customers.txt –c –U sa –P"guerilla"
bcp pubs..customer in customers.txt –f customers.fmt –U sa    –P"guerilla"

Table 7-2 Key Parameters Used with BCP

Parameter	Description
dbname	The name of the database. This parameter is optional and if it's not supplied, the user's default database is used.
owner	The owner of the table or view being used. Use the .. syntax for a general owner, such as pubs .. authors instead of pubs.dbo.authors.
tablename	The name of the table to access. Use the # or ## syntax to copy a temporary table.
viewname	The name of the destination view when copying data into SQL Server; the source view when copying data from SQL Server.
query	T-SQL statement that generates a result set. Using double quotation marks and specifying the queryout parameter are mandatory with this option.
in	Specifies an import process.
out	Specifies an export process.
format	Sets the creation of a format file. You must set the name of the format file with the –f switch and also specify the format for this file with –n, -c, -w, or –V.
queryout	Must be used when exporting output from a SQL query or stored procedure.
datafile	The name of the file for importing or the name of the file to create when exporting. This can include the full file path.

BCP also supports a wide variety of switches. These switches and their associated parameters are summarized in Table 7-3.

Table 7-3 Switches Used with BCP

Switch	Description
-a packetsize	Sets the number of bytes in a network packet. Default is 4096 on Windows NT and 512 for MS-DOS. The valid range is 512 to 65,535 bytes.
-b batchsize	The number of rows to transfer in the batch. Each batch is copied to the server as one transaction. By default all rows are copied in a single batch. Don't use with the -h ROWS_PER_BATCH option.
-c	Character data mode (ASCII text) for transfers to and from non-SQL Server products.
-C codepage	Code page being used by the import file. Only relevant when the data contains char, varchar, or text columns with character values greater than 127 or less than 32. Use the code page value ACP with ANSI ISO 1252 data, RAW when no conversion should occur, OEM to use the client's default code page, or type a specific code page value, such as 850.
-e errfile	Stores error messages in the specified error file.
-E	Uses identity values. Otherwise identity values are ignored and automatically assigned new ones.
-F firstrow	Sets the number of the first row to use.
-f formatfile	Sets the name and path to a BCP format file. The default filename is BCP.FMT.
-h "load hints"	Used to set load hints: ROWS_PER_BATCH, KILOBYTES_PER_BATCH, TABLOCK, CHECK_CONSTRAINTS, and ORDER.
-i inputfile	Sets the name of a response file, containing responses to the command prompt questions for each field when performing a bulk copy using interactive mode.
-k	Preserves null values.
-L lastrow	Sets the last row to use.
-m maxerrors	Sets the maximum number of errors that can occur before terminating BCP. The default is 10.
-N	Sets native export for noncharacter data and Unicode character export for character data.
-n	Sets native data mode, which is SQL Server-specific.
-o outfile	File to redirect output of BCP during unattended operation.
-P password	Password to use to log on.
-q	Uses quoted identifiers.
-R	Enables regional format copy for currency, date, and time data.
-r rowterminator	Sets the row terminator.
-S servername	Sets the SQL Server name.
-t fieldterminator	Sets the field terminator.
-T	Uses a trusted connection.
-U username	Sets the username for logon.
-V	Sets the data type version for native and character formats. For SQL Server 6.0 format use 60, for SQL Server 6.5 use 65, and for SQL Server 7.0 use 70.
-v	Displays the BCP version.
-w	Sets wide character (Unicode) mode.

BCP Permissions and Modes

Although any user can run BCP, only users with appropriate permissions can access SQL Server and the specified database objects. When you run BCP, you can set logon information using the U and P switches. For unattended operations, using these switches is essential to ensure that permissions are granted appropriately. To import data into a table, the user needs Insert permission on the target table. To export data from a table, the user needs Select permission for the source table.

BCP can use three different modes:

Character mode Used when you want to import or export data as ASCII text. The switch to set this mode is c.
Native mode Used when you want to import or export data in native format. The switch to set this mode is n or N.
Wide mode Used when you want to import or export data as Unicode text. The switch to set this mode is w.

The character and wide modes are best when copying to a non-SQL Server product. Use native mode when you're copying data between SQL Server tables. These modes all have their strengths and weaknesses. With character or wide mode files, you can view the contents and make sure that you have the right data set, but for imports you must also tell SQL Server how this data is formatted. You can do this through interactive prompts or by using a format file containing the responses to these prompts. With native mode, on the other hand, there's a tradeoff. You can't view the contents of native data files, but you don't have to specify data formatting information when importing files either.

Importing Data with BCP

With BCP you can import data in two ways. You can start an interactive session or you can set the necessary responses in a format file. The following example shows how you could start an interactive session.

bcp pubs..customer in customers.txt

To specify a format file, you use the f flag, such as

bcp pubs..customer in customers.txt –w –f customers.fmt

In an interactive session, BCP prompts you for information needed to complete the import or export process. BCP starts an interactive session when either of the following situations occur:

You import without specifying the –c, -n, -w, or –N parameters.
You export without specifying the –c, -n, -w, or –N parameters.

The interactive session allows you to customize the BCP process much as you do with a format file. In fact, before you try to create a format file, you should run BCP in interactive mode and then choose to have BCP create the necessary format file for you. This operation will give you a good idea of how to configure the format file.

For each column in a table you're importing, you'll see the following prompts during an interactive session:

Enter the file storage type of field [char]:
Enter prefix length of field [0]:
Enter length of field [5]:
Enter field terminator [none]:

Tip Pressing Enter accepts the default values. To skip a column in an import file, type 0 for the prefix length, 0 for the field length, and none for the terminator type. You can't skip a column when exporting data.

These prompts ask you to type various kinds of information, and in every case the default value for the current column is shown in brackets. At the end of the interactive session, you'll be asked if you want to save your responses in a format file. If you answer Y, you can type the name of the format file when prompted, such as

Do you want to save this format information in a file? [Y/N]
Host filename [bcp.fmt]: customers.fmt

You can then use the format file for other BCP sessions by setting the f switch as specified earlier. Figure 7-20 shows a sample format file. Because the format file has a rigid syntax that you must follow, I recommend creating a sample file to get started. Each line in the file contains information fields that determine how data should be imported. These lines tell you the following information:

The first line sets the version of BCP used. Here the version is 8.0.
The second line sets the number of columns in the table you're importing. In the example the table contains 11 columns.
Subsequent lines set the formatting of each column in the table from the first column to the last column.

Figure 7-20: Format files are written in ASCII text and you can view them in Microsoft Notepad or any other text editor.

The lines defining table columns are broken down into fields with each field setting a different input parameter. Normally, these fields are separated by spaces. The number of spaces doesn't really matter—provided there is at least one space. BCP treats one or more spaces as a field separator. File format fields operate in the following manner:

Field 1 sets the column number you're describing from the data file.
Field 2 sets the file storage type, which is simply the data type of the column.
Field 3 sets the prefix length for compacted data. A value of zero specifies that no prefix is used.
Field 4 sets the field length, which is the number of bytes required to store the data type. Use the default value provided whenever possible.
Field 5 sets the field terminator. By default, BCP separates all fields but the last one with tabs (\t) and separates the last field with a carriage return and line field (\r\n).
Field 6 sets the table column number in the database. For example, a value of 1 says the column corresponds to the first column in the database.
Field 7 sets the table column name.

Exporting Data with BCP

When you export data, BCP creates a data file using the name you specify. With nonnative files (ASCII and Unicode text), the columns in this file are separated with tabs by default, with the last column having a carriage return and line feed. You specify a tab as a terminator with \t and carriage return and line feed with \r\n. In a format file, a tab can be an actual tab character or a series of five or more spaces.

As with importing data, you can handle data export interactively. For example, if you start an export session without specifying format information, you're prompted for this information. In the following example you export a table to a file called customers.txt and use semicolons as the delimiter:

bcp pubs..customer out customers.txt –c –t;

BCP Scripts

A BCP script is simply a batch file or a Windows Script Host file containing BCP commands. Sample 7-4 shows examples of how you could run BCP using various scripting options. If you don't know how to use batch files or Windows Script Host, two great resources are Windows NT Scripting Administrator's Guide (IDG Books, 1999) and Windows 2000 Scripting Bible (IDG Books, 2000).

Sample 7-4 Using BCP in a Script

sched-export.bat

@echo off
@if not "%OS%"=="Windows_NT" goto :EXIT
bcp pubs..customer out customers.txt –c –t, -Usa –P"guerilla"
:EXIT

sched-export.vbs

'Nightly Bulk Copy export for the customers table
'Writes output to cust.txt and errors to err.txt
Set ws = WScript.CreateObject("WScript.Shell")
ret = ws.Run("bcp pubs..customers out cust.txt -c -t, -Usa
   –Pguerilla –eerr.txt",0,"TRUE")

sched-export.js

\\Nightly Bulk Copy export for the customers table\\Writes output to cust.txt and errors to err.txt
var ws = WScript.CreateObject("WScript.Shell");
ret = ws.Run("bcp pubs..customers out cust.txt -c -t, -Usa
   –Pguerilla –eerr.txt",0,"TRUE")

After you create a script file for the bulk copy command, you can schedule it as a task to run on your system. To schedule these scripts to run every night at midnight, you could use the following commands.

AT 00:00 /every:M,T,W,Th,F,S,Su "sched-export.bat"
AT 00:00 /every:M,T,W,Th,F,S,Su "cscript //B sched-export.js"
AT 00:00 /every:M,T,W,Th,F,S,Su "cscript //B sched-export.vbs"

More Info For more information on scheduling tasks, see Microsoft Windows NT Server 4.0 Administrator's Pocket Consultant (Microsoft Press, 1999) or Microsoft Windows 2000 Administrator's Pocket Consultant (Microsoft Press, 2000).

BULK INSERT

A Transact-SQL command for importing data into a database is BULK INSERT. You can use BULK INSERT in much the same way as you use BCP. In fact, most

of the parameters for BULK INSERT are the same as those used with BCP—they just have a different syntax. This syntax is shown as Sample 7-5.

Sample 7-5 BULK INSERT Syntax and Usage

Syntax

BULK INSERT [['dbname'.]['owner'].]{'tablename' FROM datafile}
   [WITH
   (
   [ BATCHSIZE [= batch_size]]
   [[,] CHECK_CONSTRAINTS]
   [[,] CODEPAGE [= 'ACP' | 'OEM' | 'RAW' | 'code_page']]
   [[,] DATAFILETYPE [=
   {'char' | 'native'| 'widechar' | 'widenative'}]]
   [[,] FIELDTERMINATOR [= 'field_terminator']]
   [[,] FIRSTROW [= first_row]]
   [[,] FIRETRIGGERS [= fire_triggers]]
   [[,] FORMATFILE [= 'format_file_path']]
   [[,] KEEPIDENTITY]
   [[,] KEEPNULLS]
   [[,] KILOBYTES_PER_BATCH [= kilobytes_per_batch]]
   [[,] LASTROW [= last_row]]
   [[,] MAXERRORS [= max_errors]]
   [[,] ORDER ({column [ASC | DESC]} [,...n])]
   [[,] ROWS_PER_BATCH [= rows_per_batch]]
   [[,] ROWTERMINATOR [= 'row_terminator']]
   [[,] TABLOCK]
   )
   ]

Usage

BULK INSERT pubs..customers FROM 'c:\data\customer.txt'
BULK INSERT pubs..customers FROM 'c:\cust.txt' with
   (DATAFILETYPE = 'char',
   FORMATFILE='c:\cust.fmt')

In order to use BULK INSERT, you must set the database properties option Select Into/Bulk Copy. To set this option, open the Properties dialog box for the database you want to work with, choose the Options tab, and then select the Select Into/Bulk Copy check box.

Click to order

Importing and Exporting Data with DTS and BCP

On This Page

Understanding DTS

DTS and Database Drivers

DTS Packages

Other DTS Tools

Creating Packages with the DTS Import/Export Wizard

Stage 1: Source and Destination Configuration

Stage 2: Copy, Query, or Transfer

Stage 3: Formatting and Transformation

Stage 4: Save, Schedule, and Replicate

Working with DTS Packages

Examining, Running, and Scheduling Packages

Managing Packages Using the DTS Run Utility for Windows

Managing Packages from the Command Line

Understanding BCP

BCP Basics

BCP Syntax

BCP Permissions and Modes

Importing Data with BCP

Exporting Data with BCP

BCP Scripts

BULK INSERT

Additional resources