The DTS Bulk Export Task was originally conceived in response to a problem with the OLE-DB provider for text files. For more details see DataPump truncates delimited fields to 255 characters. Whilst this problem can be fixed quite easily the Bulk Export Task still offers some benefits over the DataPump due to it's simplicity, for example it doesn't require you to define transformations in advance. When using the DataPump task you can change the source table, but if the table structure is different, you need to update the transformations as well. Since the Bulk Export Task doesn't know the table structure in advance it doesn't mind if the table has a different structure. (The exception to this is of course if you have defined a format file as opposed to user defined delimiters for the columns and rows.) The task makes an ideal alternative to scenarios where previously you may have used the bcp utility, but would prefer to harness the flexibility of DTS using a native custom task.

This task uses the SQL-DMO BulkCopy object to provide fast performance and also avoid the 255 character issue mentioned above. The custom task is written in Visual Basic 6.0. Due to the limited threading model of Visual Basic it must execute on the main package thread. The DTS designer will enforce this by default, but any other tasks that interact with the Bulk Export Task such as an ActiveX Script or a Dynamic Properties Task must also be set to execute on the main package thread. This also includes the Execute Package Task where the Bulk Export Task is present in the child package. These restrictions are present for any task written in Visual Basic such as the Microsoft supplied OLAP Processing Task.

The Bulk Export Task is now provided as a windows installer package (MSI) that covers installation and task registration. Full documentation of properties and constants is provided below, followed by a simple example for manipulating the task properties at run-time. For an additional example of using the Bulk Export Task see How to export all tables in a database.

You can download the Bulk Export Task here.

Property	Data Type	Description
ColumnDelimiter	String	Column delimiter used when exporting data.
DatabaseName	String	Name of the database in which table or view to export resides.
DataFileType	DTSBulkExportDataFileType	Value of the DTSBulkExportDataFileType constant indicating the type/format of export data file.
Description	String	The textual description of a task.
ExportFile	String	Specifies the path of the file to be created when data is exported.
Flags	DTSBulkExportSecurity	Value of the DTSBulkExportSecurity constant indicating user authentication type.
FormatFile	String	Specifies the path of the format file to be used when exporting data.
LogFile	String	Specifies the path of the log file to be created.
Name	String	The name of the task.
ObjectName	String	The name of the object to export, e.g. MyTable, [Bob].[Table Bob]
ObjectType	DTSBulkExportObjectType	Value of the DTSBulkExportObjectType constant indicating the type of object to be exported.
RowDelimiter	String	Row delimiter used when exporting data.
RowsComplete	String	Number of rows exported. (Read Only).
ServerName	String	Name of the SQL Server.
UserName	String	User name for SQL Server authentication.
UserPassword	String	User name for SQL Server authentication. (Write Only).

Constant	Value	Description
DTSBulkExportDataFileType_UserDefined	3	Data file format is defined by a Format file (FormatFile property).
DTSBulkExportDataFileType_NativeFormat	4	Data file is SQL Server bulk copy native format.
DTSBulkExportDataFileType_UseFormatFile	5	Data file format is user defined (ColumnDelimiter property, RowDelimiter property).

Constant	Value	Description
DTSBulkExportObjectType_Table	0	Export object, as defined by ObjectName property, is a table.
DTSBulkExportObjectType_View	1	Export object, as defined by ObjectName property, is a view.

Constant	Value	Description
DTSBulkExport_Default	0	Use SQL Server authentication to connect to an instance of SQL Server.
DTSBulkExport_UseTrustedConnection	256	Use Windows authentication to connect to an instance of SQL Server.

Below is a sample piece of ActiveX Script code used to change the ObjectName to a new value stored in a global variable. The task properties can only be accessed through the Properties collection as shown, and do not expose themselves directly at the custom task object level.

' Pkg 237 (BulkExportTask)
Option Explicit

Function Main()

	Dim oPkg, oBulkExport
	Dim sTableName

	sTableName = DTSGlobalVariables("TableName").Value

	Set oPkg = DTSGlobalVariables.Parent
	Set oBulkExport = oPkg.Tasks("DTSTask_DTSBulkExport.ExportData_1").CustomTask

	oBulkExport.Properties("ObjectName").Value = sTableName

	Set oBulkExport = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

You can download the Bulk Export Task here.

Version 2.2.0.6 - Added support for SQL Server bulk copy native format..
(22 Apr 2004)

Version 2.2.0.2 - Added support for object owner. Correctly display long database and object names in drop down boxes.
(8 Sep 2003)

Version 2.2.0.0 - Constants publicized. Task now delivered through MSI installer package. Binary compatibility broken with previous versions.
(1 Sep 2003)

Version 2.0.0.2 - Fixed bug with SQL Server authentication password handling.
(26 Mar 2002)

Version 2.0.0.0 - Public Release.
(16 Feb 2002)