Programming4us
         
 
 
SQL Server

SQL Server Integration Services : Using bcp (part 3)

11/30/2010 3:11:58 PM

File Data Types

bcp can handle data in one of three forms: character (ASCII), native, or Unicode. You have the choice of which character format is used, depending on the source or destination of the data file:

  • The character format (–c) is the most commonly used of the three data types because it reads or writes using ASCII characters and carries out the appropriate data type conversion for the SQL Server representations. The CHAR data type is the default storage type; it uses tabs as field separators and the newline character as the row terminator.

  • The native format (–n) is used for copying data between servers. This format allows bcp to read and write using the same data types used by server, which results in a performance gain. This format does, however, render the data file unreadable by any other means.

  • The Unicode option (–w) uses Unicode characters rather than ASCII characters. The NCHAR data type is the default storage type; it uses tabs as field separators and the newline character as the row terminator.

Format Files

By using a format file, you can customize the data file created by bcp or specify complex field layouts for data loads. There are two ways to create a format file: by using interactive bcp and by using the format switch.

Customizing a Format File by Using Interactive bcp

If you do not specify one of the –n, –c, or –w data type format switches, bcp (in or out) prompts you for the following information for each column in the data set:

bcp offers a default for each of these prompts that you can either accept or reject. If you accept all the defaults, you wind up with the same format file you would have by specifying the native format (with the –n switch). The prompts look like this:

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

or like this:

Enter the file storage type of field ProductID [int]:
Enter prefix length of field ProductID [0]:
Enter field terminator [none]:

By pressing the Enter key at the prompt, you accept the default. Alternatively, you can type your own value at the prompt if you know the new value and it is different from the default.

Creating a Format File by Using the format Switch

By using the format option, you can create a format file without actually transferring any data. Here is an example of creating a format file for the SalesOrderHeaderAdventureWorks2008 database: table in the

C:> BCP AdventureWorks2008.Sales.SalesOrderHeader format orders.dat
–S DBARCH-LT2\SQL08DE01
–U sa –P xyz –f orders.fmt –c

The format file created looks like this:

10.0
27
1 SQLCHAR 0 12 ""\t"" 1 SalesOrderID """"
2 SQLCHAR 0 5 ""\t"" 2 RevisionNumber """"
3 SQLCHAR 0 24 ""\t"" 3 OrderDate """"
4 SQLCHAR 0 24 ""\t"" 4 DueDate """"
5 SQLCHAR 0 24 ""\t"" 5 ShipDate """"
6 SQLCHAR 0 5 ""\t"" 6 Status """"
7 SQLCHAR 0 3 ""\t"" 7 OnlineOrderFlag """"
8 SQLCHAR 0 50 ""\t"" 8 SalesOrderNumber SQL_...
9 SQLCHAR 0 50 ""\t"" 9 PurchaseOrderNumber SQL_...
10 SQLCHAR 0 30 ""\t"" 10 AccountNumber SQL_...
11 SQLCHAR 0 12 ""\t"" 11 CustomerID """"
12 SQLCHAR 0 12 ""\t"" 12 ContactID """"
13 SQLCHAR 0 12 ""\t"" 13 SalesPersonID """"
14 SQLCHAR 0 12 ""\t"" 14 TerritoryID """"
15 SQLCHAR 0 12 ""\t"" 15 BillToAddressID """"
16 SQLCHAR 0 12 ""\t"" 16 ShipToAddressID """"
17 SQLCHAR 0 12 ""\t"" 17 ShipMethodID """"
18 SQLCHAR 0 12 ""\t"" 18 CreditCardID """"
19 SQLCHAR 0 15 ""\t"" 19 CreditCardApprovalCode SQL_...
20 SQLCHAR 0 12 ""\t"" 20 CurrencyRateID """"
21 SQLCHAR 0 30 ""\t"" 21 SubTotal """"
22 SQLCHAR 0 30 ""\t"" 22 TaxAmt """"
23 SQLCHAR 0 30 ""\t"" 23 Freight """"
24 SQLCHAR 0 30 ""\t"" 24 TotalDue """"
25 SQLCHAR 0 256 ""\t"" 25 Comment SQL_...
26 SQLCHAR 0 37 ""\t"" 26 rowguid """"
27 SQLCHAR 0 24 ""\r\n"" 27 ModifiedDate """"


The following is a description of the lines and columns in the preceding format file example:
  • The first line shows the version of bcp.

  • The second line shows the number of columns.

  • The third line, first column shows the data field position.

  • The third line, second column shows the data type.

  • The third line, third column shows the prefix.

  • The third line, fourth column shows the data file field length.

  • The third line, fifth column shows the field or row terminator.

  • The third line, sixth column shows the column position.

  • The third line, seventh column shows the column name.

  • The third line, eighth column shows the column collation.

You get different format files depending on your table and whether you chose character, native, or Unicode as the data type. As you can see in the preceding example, only the last two columns in the format file relate to the actual table; the remaining columns specify properties of the data file.

Other -----------------
- SQL Server Integration Services : Using bcp (part 2) - Fundamentals of Exporting and Importing Data
- SQL Server Integration Services : Using bcp (part 1)
- SQL Server Integration Services : Connection Projects in Visual Studio
- SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility
- SQL Server Integration Services : The Package Execution Utility (part 2) - Running Packages
- SQL Server Integration Services : The Package Execution Utility (part 1)
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
 
 
Most View
- sharepoint 2007 : Search in WSS
- BizTalk 2010 Recipes : Business Rules Framework - Calling the Business Rules Engine from an Orchestration
- The Art of SEO : Measuring Search Traffic (part 1)
- Windows Phone 7 : Uninstalling an App
- Programming WCF Services : Queued Services - Delivery Failures (part 2) - Processing the Dead-Letter Queue
- Understanding and Installing Active Directory Rights Management Services (part 3)
- SQL server 2012 : T-SQL Enhancements - Table-Valued Parameters (part 2)
- Windows 7 : Controlling and Customizing Your Website (part 2) - Setting the Website’s Default Document
- Windows 7 : Using Any Search Engine from the Address Bar
- Microsoft Exchange Server 2003: Configuring Recipient Objects (part 8) - Moving Mailboxes with the Microsoft Exchange Mailbox Merge Wizard
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS