BulkInsertTask Object
The BulkInsertTask object, based on the Transact-SQL BULK INSERT statement, provides the fastest method for copying large amounts of data from a text file to Microsoft® SQL Server™. Use BulkInsertTask for copying operations, and in situations where performance is the most important consideration. It is not used in conjunction with transformations during data import operations.
Collections
| Properties Collection |
Properties
Methods
| Execute Method |
Remarks
A Connection2 object must be used to access the database into which data is inserted. You can specify the format of the input data file directly using the FieldTerminator and RowTerminator properties, or indirectly through a bcp format file.
Example
The following Microsoft Visual Basic® code uses the BulkInsertTask object to insert data from file D:\DTS_UE\BCPData\Payroll.txt into table Payroll of database DTS_UE.
Public Sub Main()
'initialize Payroll table in DTS_UE db with bulk data
Dim objPackage As DTS.Package2
Dim objConnect As DTS.Connection2
Dim objStep As DTS.Step
Dim objTask As DTS.Task
Dim objBulkCopy As DTS.BulkInsertTask
Set objPackage = New DTS.Package
'create database connection
Set objConnect = objPackage.Connections.New("SQLOLEDB.1")
With objConnect
.ID = 1
.DataSource = "(local)"
.UseTrustedConnection = True
End With
objPackage.Connections.Add objConnect
'create step and task, specify data file and format
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSBulkInsertTask")
Set objBulkCopy = objTask.CustomTask
With objBulkCopy
.Name = "BulkInsTask"
.DataFile = "D:\DTS_UE\BCPData\Payroll.txt"
.ConnectionID = 1
.DestinationTableName = "DTS_UE..Payroll"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
'link step to task to package, run package
objStep.TaskName = objBulkCopy.Name
objStep.Name = "BulkInsStep"
With objPackage
.Steps.Add objStep
.Tasks.Add objTask
.FailOnError = True
.Execute
End With
End Sub