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)