PRB: A DTS Package That You Save as a Visual Basic File May Fail to Import Tab-Delimited Text Data (320304)

SYMPTOMS

If you save a Data Transformation Services (DTS) package as a Microsoft Visual Basic file, it may fail to import tab-delimited text data when you run the package from Microsoft Visual Basic, and you receive the following error message:

Copy data from 'transformation task name' Step failed. Microsoft Data Transformation Services (DTS) Data Pump Column Name 'column_name' was not found.

NOTE: The column name in the error message may vary depending on whether the first row of the input file contains column headings.

This error occurs on SQL Server 2000 Service Pack 1 (SP1) and SQL Server 2000 Service Pack 2 (SP2).

If you save the same DTS package as a Visual Basic file while running SQL Server 2000 RTM, the package runs from Visual Basic without errors; however, if the input file contains only one row, the row may fail to import, but no error is generated.

CAUSE

This behavior occurs because the column delimiter for the file is changed to spaces (opening quotation marks followed by eight spaces and then closing quotation marks):

oConnection.ConnectionProperties("Column Delimiter") = "        "

WORKAROUND

To work around this behavior, modify the Visual Basic file and change the ConnectionProperties("Column Delimiter") property of the text file to vbTab.

For example:

oConnection.ConnectionProperties("Column Delimiter") =  vbTab

MORE INFORMATION

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

221193 INF: How to Install DTS Event Handlers in Visual Basic

240221 INF: How to Handle Errors in DTS Package and Step Objects