F
IGURE
11.3
Text qualifiers are needed when commas occur in the data of a comma-delimited text file. Use the Transform Data task
for these files.
The second way to create a format file is to use the bcp utility interactively.
Open a Command Prompt and type in a bcp command. The following command could be used
to generate the format file in Listing 11.1:
bcp pubs.dbo.stores out c:\temp\stores.txt -Usa
The bcp utility will ask you a number of questions about the fields in this bulk copy. One of
the last questions you will be asked is whether or not you want to create a format file. If you
say yes, you will be asked for the host filename, which is used as the name of the format file
that will be created.
Reconciling Differences Between the Source and
the Destination
By default, a bulk insert takes data from the fields of a source file and puts it into the same
number of fields, using the same order, in the data destination. If you don’t have the same
number of fields or if the fields are in a different order, you usually have three options:
•Use a view in place of the destination table. Create the view so that its fields line up with
the fields of the source text file. This is usually the easiest option to implement.
•Use a format file. This option is usually harder to implement, but it gives the most
flexibility.
• Change the destination table so its fields match the fields in the text file.
Other Data Movement and Manipulation Tasks
P
ART
III
276
15 0672320118 CH11 11/13/00 5:01 PM Page 276
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Extra Fields in the Data Destination Table

2 SQLCHAR 0 40 “” 2 stor_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 40 “\r\n” 3 stor_address SQL_Latin1_General_CP1_CI_AS
The Bulk Insert Task
C
HAPTER
11
11
T
HE
B
ULK
I
NSERT
T
ASK
277
15 0672320118 CH11 11/13/00 5:01 PM Page 277
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
3. Add the missing fields in the order they appear in the destination, using 0 for the column
length and 0 for the column order field.
4. If you have a row delimiter (in the example, the new line character), move that to the last
line.
5. Change the number in the second row of the format file to the number of fields in the
destination table.
When you are done, your format file should look like Listing 11.3.
L
ISTING
11.3
This Format File Accommodates Extra Fields in the Data Destination Table
8.0

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
L
ISTING
11.4
A Generated Format File
7.0
6
1 SQLCHAR 0 40 “” 1 stor_name
2 SQLCHAR 0 4 “” 2 stor_id
3 SQLCHAR 0 40 “” 3 stor_address
4 SQLCHAR 0 20 “” 4 city
5 SQLCHAR 0 2 “” 5 state
6 SQLCHAR 0 5 “” 6 zip
The rows describing the fields in the format file must be in the order that those rows appear in
the source text file. But the numbers in the sixth column must reflect the actual order of those
fields in the destination table. Listing 11.5 shows a format file adjusting the order of fields that
differ in the source and destination tables.
L
ISTING
11.5
Switching the Numbering in Column 6 Reorders Fields as They Enter the
Destination Table
7.0
6
1 SQLCHAR 0 40 “” 2 stor_name
2 SQLCHAR 0 4 “” 1 stor_id
3 SQLCHAR 0 40 “” 3 stor_address
4 SQLCHAR 0 20 “” 4 city
5 SQLCHAR 0 2 “” 5 state
6 SQLCHAR 0 5 “” 6 zip

You could follow these steps:
1. Create a temporary table that has the same structure as the source data file:
create table tmpStoresForBulkInsertExtraFields
(
[stor_id] [char] (4) NOT NULL,
[manager_name] char(40) NULL,
[stor_name] [varchar] (40) NULL,
[stor_address] [varchar] (40) NULL,
[city] [varchar] (20) NULL,
[state] [char] (2) NULL,
[zip] [varchar] (50) NULL,
[stor_type] char(40) NULL,
[stor_descript] char(40) NULL
)
2. Generate a format file using the temporary table as the destination for the Bulk Insert
task. Your generated format file will look like this:
7.0
9
1 SQLCHAR 0 4 “,” 1 stor_id
2 SQLCHAR 0 40 “,” 2 manager_name
3 SQLCHAR 0 40 “,” 3 stor_name
4 SQLCHAR 0 40 “,” 4 stor_address
5 SQLCHAR 0 20 “,” 5 city
6 SQLCHAR 0 2 “,” 6 state
7 SQLCHAR 0 5 “,” 7 zip
8 SQLCHAR 0 40 “,” 8 stor_type
9 SQLCHAR 0 40 “\r\n” 9 stor_descript
3. Renumber the destination column order to reflect the actual order of fields in the destina-
tion. Set the value to 0 for those fields that don’t exist in the destination.
When you’re done, the format file should look like Listing 11.6.

11
11
T
HE
B
ULK
I
NSERT
T
ASK
281
F
IGURE
11.4
Many settings on the Options tab of the Bulk Insert Task Properties dialog greatly affect performance.
The code sample at the end of this chapter shows how to set all these properties in Visual Basic
code.
Check Constraints
When this option is selected, the data is checked for compliance with all constraints as it is
added to the destination table. By default, constraints are ignored when adding records with a
Bulk Insert:
Default value:
False
Effect on performance: Decreases performance when selected
Object property:
CheckConstraints
Equivalent parameter of the Bulk Insert command:
CHECK_CONSTRAINTS
Equivalent parameter of bcp:
-h “CHECK_CONSTRAINTS”

to be enforced and all the update triggers to fire. The command will fail if any record
in the table violates one of the constraints.
All the update triggers will be run by this command. If you take all your insert trig-
gers and also make them update triggers, this code activates all the triggers that
were missed during the Bulk Insert. If any of the triggers fails to be successfully com-
pleted, this update command will also fail.
You need more complex code to clean up the data if it fails this constraint and trig-
ger test.
N
OTE
Keep Nulls
Selecting this option causes null values to be inserted into the destination table wherever there
are empty values in the source. The default behavior is to insert the values that have been
defined in the destination table as defaults wherever there are empty fields.
Default value:
False
Effect on performance: Improves performance when selected
Object property:
KeepNulls
Equivalent parameter of the Bulk Insert command:
KEEPNULLS
Equivalent parameters of bcp:
-k
15 0672320118 CH11 11/13/00 5:01 PM Page 282
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
A Bulk Insert task that keeps nulls could run faster. You can create an Execute SQL task after
the Bulk Insert that will apply the table’s defaults. Here is a SQL statement that puts the
default value into all the PhoneNumber fields that have empty values:
Update tblCustomer set PhoneNumber = Default where PhoneNumber = Null
This strategy assumes that there are no records in the PhoneNumber field where you intention-

mechanism either by selecting this property or using
sp_tableoption
to set the “table lock on
bulk load” option to
True
.
Default value:
False
Effect on performance: Significantly improves performance when selected
Object property:
TableLock
Equivalent parameter of the Bulk Insert command:
TABLOCK
Equivalent parameters of bcp:
-h “TABLOCK”
The Bulk Insert Task
C
HAPTER
11
11
T
HE
B
ULK
I
NSERT
T
ASK
283
15 0672320118 CH11 11/13/00 5:01 PM Page 283

specified, this property is ignored.
The ordering string is constructed in the same way as the syntax of the
ORDER BY
clause in a
SQL statement. If the ordering of customers were alphabetical by city and oldest to youngest
within a city, the ordering string would be
City, Age DESC
Code Page
This option specifies the code page that has been used for the data in the source file. This prop-
erty affects the Bulk Insert only in cases where there are characters with values less than 32 or
greater than 127.
Default value: OEM
Other possible values: ACP, RAW, Specific code page number
Other Data Movement and Manipulation Tasks
P
ART
III
284
15 0672320118 CH11 11/13/00 5:01 PM Page 284
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Effect on performance: Usually none
Object property:
CodePage
Equivalent parameter of the Bulk Insert command:
CODEPAGE
Equivalent parameters of bcp:
-C
Data File Type
There are two choices to make in this property—the choice between
char

Effect on performance: Using
native
and
widenative
improves performance when you’re
using a text file to transfer data from one SQL Server to another.
Object property:
DataFileType
Equivalent parameter of the Bulk Insert command:
DATAFILETYPE
Equivalent parameters of bcp:
-s
for native,
-w
for wide character
Insert Commit Size
By default, all records are inserted into the destination table as a single transaction. This prop-
erty allows for fewer records to be included in each transaction. If a failure takes place during
The Bulk Insert Task
C
HAPTER
11
11
T
HE
B
ULK
I
NSERT
T

P
ART
III
286
I have not been able to use this property with the Bulk Insert task. No matter what
values I set for the
MaximumErrors
property and the
BatchSize
property, the task still
fails with the first error.
N
OTE
Default value: 10
Effect on performance: None
Object property:
MaximumErrors
Equivalent parameter of the Bulk Insert command:
MAXERRORS
Equivalent parameters of bcp:
-m
Only Copy Selected Rows, Starting with Row, and
Stopping at Row
These properties allow you to choose to include only a particular range of records from the
source data file in your bulk insert.
15 0672320118 CH11 11/13/00 5:01 PM Page 286
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Default values: Not selected, 0, and 0. All the records in the file are included in the bulk insert.
Effect on performance: None
Object Properties:

reference to the Microsoft DTSPackage Object Library.
L
ISTING
11.7
The Visual Basic Code to Create a Bulk Insert Task
Option Explicit
Public Function fctCreateBulkInsertTask( _
pkg As DTS.Package2, _
Optional sBaseName As String = “BulkInsertTask”, _
Optional sDataSource As String = “(local)”, _
Optional sConnectionUserID As String = “”, _
Optional sConnectionPassword As String = “”, _
Optional sCatalog As String = “pubs”, _
Optional sDestinationTableName As String = “stores”, _
Optional sDataFile As String = “”, _
Optional sExistingConnection As String = “”, _
Optional lBatchSize As Long = 0, _
The Bulk Insert Task
C
HAPTER
11
11
T
HE
B
ULK
I
NSERT
T
ASK

.Catalog = sCatalog
.UserID = sConnectionUserID
.Password = sConnectionPassword
‘If User ID is empty string, use trusted connection
If sConnectionUserID = “” Then
.UseTrustedConnection = True
Else
.UseTrustedConnection = False
End If
End With
pkg.Connections.Add con
Other Data Movement and Manipulation Tasks
P
ART
III
288
L
ISTING
11.7
Continued
15 0672320118 CH11 11/13/00 5:01 PM Page 288
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Else
Set con = pkg.Connections(sExistingConnection)
End If
‘Create task and custom task
Set tsk = pkg.Tasks.New(“DTSBulkInsertTask”)
Set cus = tsk.CustomTask
With cus
‘Set ConnectionID

The Bulk Insert Task
C
HAPTER
11
11
T
HE
B
ULK
I
NSERT
T
ASK
289
L
ISTING
11.7
Continued
15 0672320118 CH11 11/13/00 5:01 PM Page 289
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
pkg.Steps.Add stp
fctCreateBulkInsertTask = stp.Name
Set con = Nothing
Set tsk = Nothing
Set cus = Nothing
Set stp = Nothing
ProcExit:
Exit Function
ProcErr:
MsgBox Err.Number & “ - “ & Err.Description

• Using an Output Parameter for the
Rowset 299
• Dynamically Modifying the SQL
Statement 300
• Using the Execute SQL Task to Execute a DTS
Package from a Remote Server 301
•Creating an Execute SQL Task in Visual
Basic 306
16 0672320118 CH12 11/13/00 4:58 PM Page 291
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Other Data Movement and Manipulation Tasks
P
ART
III
292
Microsoft has significantly improved and extended the value of the Execute SQL task in SQL
Server 2000. You can now do the following:
•Use global variables to dynamically modify the query used in the task.
•Use global variables to receive the value of fields for one record returned by a
SELECT
query.
•Use a global variable to receive a reference to the recordset returned by a
SELECT
query.
This recordset can then be referenced in ActiveX scripts as if it were an ADO recordset.
When to Use the Execute SQL Task
The transformation tasks allow you to perform rapid row-by-row processing of your data. The
Execute SQL task gives you the power of SQL-oriented set processing, which will usually be
even faster. If you can write your data transformation as a SQL statement and you don’t need
to use special processing for individual rows, you can usually use an Execute SQL task.

DENY
, and
REVOKE
.
•Stored procedures.
• DTS packages, by using SQL Server’s OLE Automation stored procedures.
Creating the Execute SQL Task
You can create an Execute SQL task in the Package Designer or with code. The last section of
this chapter shows how to create an Execute SQL task using code.
The Import/Export Wizard creates a variety of Execute SQL tasks. It uses these tasks to drop
tables, create tables, and delete data from tables. You cannot use the wizard to create Execute
SQL tasks for other purposes.
The Package Designer’s Execute SQL Task Properties dialog is shown in Figure 12.1. It gives
you three ways to set the SQL Statement:
16 0672320118 CH12 11/13/00 4:58 PM Page 292
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
•Write it in the SQL Statement box.
•Use the Browse button to load the query from a file.
•Use the Build Query button to create a query using a visual interface.
The Execute SQL Task
C
HAPTER
12
12
T
HE
E
XECUTE
SQL T
ASK

—The ID of the connection used for the query.
•
CommandTimeout
—The length of time in seconds that the task waits for a response from
the connection. The default value is 0, which causes the task to wait forever.
•
CommandProperties
—A pointer to the collection of OLE DB properties for the
connection.
The extended SQL Server 2000 object,
ExecuteSQLTask2
, has three additional properties,
which implement the ability to use parameters with the Execute SQL task:
•
InputGlobalVariableNames
—A semicolon-delimited list of the names of the global
variables used as parameters in the query.
•
OutputAsRecordset
—The name of the global variable that is to be assigned an object
reference to the recordset returned by the query.
•
OutputGlobalVariableNames
—A semicolon-delimited list of the names of the global
variables that are to receive the data values returned by the query.
The
SQLStatement
property has been modified in
ExecuteSQLTask2
so that it can include

Where ShippedDate >= ?
And ShippedDate < DateAdd(d,1,?)
And ShipVia = ?
Figure 12.2 shows the Parameter Mapping dialog, which you use to map these parameters to
the appropriate global variables. If the global variables don’t exist, you can open the Global
Variables dialog to create them.
The Execute SQL Task
C
HAPTER
12
12
T
HE
E
XECUTE
SQL T
ASK
295
F
IGURE
12.2
You map the input parameters to the appropriate global variables in the Parameter Mapping dialog.
After mapping the global variables, you can look at the
InputGlobalVariableNames
using
Disconnected Edit. The value of this property will be the following string:
ShippedDateStart;ShippedDateEnd;ShipVia
You can set the value of the input global variables in a variety of ways:
•In a Dynamic Properties task (perhaps from values in a text file or an .INI file, or from
values retrieved by a query).