Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Bulk Export Task
By Darren Green
Version 7.0/2000
Level Beginner
Bulk Export Task UI

 

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.

Properties
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).
Constants
DTSBulkExportDataFileType
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).
DTSBulkExportObjectType
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.
DTSBulkExportSecurity
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.
Examples
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 History

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)