DTS Issue

[Rickspx]

Hi,

Iam trying to bring data from a text file to sql server table using DTS.
The last 4 characters of the last field are getting truncated after dts is executed
.I cannot extend the column
break for the last field (Beyond the red line)in dts for the text file.Is there a limitation for the length accomodated for
text files? Is there a solution or am I doing something wrong?

Please Help
Rick

 

[omarmtz]

Are you sure that the table last field has correct type and the lenght??

DTS truncate the text if the text exceeds the size of the field.

 

[tlbroadbent]

Is the file fixed length or delimited? If fixed length, are all rows the same length? Does the truncation occur on all rows or only some rows? If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fajitapete]

If the text file was created with crlf as the row termination marker the DTS package will look at the first record in the text file to determin the max length. Any record longer than that length gets truncated.

I ran into this problem a week ago, it's the way the text file was created. I used the edit/replace comand to add blanks to the first record to enable the full length of data to be seen.

I encountered this problem with SQL server 7x in a win2k os running the 2k enterprise manager.

 

[Rickspx]

Hi,

The file is fixed length.All the rows are not of the same length.The truncation occurs on some rows only.
Later I also found that some 4-5 rows were missing too.

I tried the option of inserting blanks (space)at the end of first record.
It takes the full length of the data ie truncation does not happen but after the first two rows every other row is missing in the table after DTS is executed.

The characters which are truncated can be seen (in DTS) at the begining of the next line (record)
If I give blanks at the end,the first characters of the next record can be seen at the end
for the shorter length records.

This text file contains data from a legacy system.Can somebody please suggest a solution for this?

Rick

 

[tlbroadbent]

DTS expects a fixed length file to be fixed length. You need to pad all the rows to the same length. See the following...

PRB: DTS May Skip More Rows than Specified for Fixed-Length Files

http://support.microsoft.com/default.aspx?kbid=247360

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Rickspx]

Thanks Terry , that worked.But I have to use this file on a scheduled basis to update a SQL table.Every time padding rows will not be practical.Is there any work around?

Rick

 

[tlbroadbent]

You could write an ActiveX script to read records from the file and pad them to the correct length. This could be done in the same DTS package as the import. Just add it as a step before the import.

Note: The same problem occurs when using BCP or Bulk Insert so these utilities will not help. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm