Visual Studio .NET - Error in DTS Package - Asked By Venkat K on 15-Jul-08 12:26 AM

I am getting an error while running DTS Package, Invalid delimited data: text qualified must followed by a column delimiter (except the last column).

I tried so many links from google but unable to find better solution.

And also How to debug a DTS Package?

check this... - Vasanthakumar D replied to Venkat K on 15-Jul-08 12:33 AM

Hi,

check this..

Data Transformation Services (DTS) transfer fails to generate an error when importing the last row from an input file to a SQL Server table when the following conditions are met:

The input file contains text qualifiers.
In the last row, the last column is missing the last text qualifier.
The DTS transfer imports all rows except the last row. However, if the text qualifier is missing on any row but the last row, the DTS transfer ceases with the following error:
Provider Error: 12309 (3015)
Error String: Invalid delimited data: text qualifier must be followed by a column delimiter (except the last column).
Another symptom of this problem is that the last row does not display when using SQL Enterprise Manager DTS Package Designer to preview the text file.
 
WORKAROUND
loadTOCNode(1, 'workaround');
Edit the input file to contain the last text qualifier.

STATUS

loadTOCNode(1, 'status');
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
 
MORE INFORMATION
loadTOCNode(1, 'moreinformation');
The following input file provides an example. This input file assumes the first row contains column headings.
"A","B"
"1","2"
"3","4
				
With this sample input file, the DTS transfer only imports 1 row and the last row is ignored.

 

Debug DTS - Nupur Agarwal replied to Venkat K on 15-Jul-08 12:37 AM

Hi Rajesh,

By the error message it would seem that your data file is either missing a column or the delimiter you have specified is not what is on the file. So, first point of check is your data file.

Also, if you go to ure DTS package you can -

  • add PRINT statements to check SQL
  • Parse your sql stt
  • You can create a flow to trap and write out errors/data messages On Failure

But unless uve got 2005, there is no way to step through it (if thats what you meant)

P.S---> My gut feel is its the file. You could also create a dummy file with valid input to test the DTS. This way u'll know for sure if its the data file or something else.


Hope this Helps.

GoodLuck

Check this... - Santhosh N replied to Venkat K on 15-Jul-08 12:50 AM

Have you looked at this..

http://support.microsoft.com/kb/241761

Its explaining the issue with the last column of the input file missing the Text qualifier, check that..

cheers

How to debug a DTS Package - santhosh kumar replied to Venkat K on 15-Jul-08 12:59 AM
   
Use Stop command to break at any point in a script. Be careful when you put  

that command into a script with repeatable execution such as an ActiveX

transformation in a data pump task. Make sure the option 'Turn on

just-in-time debugging' is set in the Data Transformation Services node,

Properties context menu in EM.

&

Check this link also
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1137289,00.html

Regards,
Santhosh

thank you very much dear - Venkat K replied to Vasanthakumar D on 15-Jul-08 05:44 AM
it resolved my problem in 3 mins. Anyways i am new to DTS Packages thanx a lot once again.