Using text files in SQL Server 2000 DTS

Question

Using text files in SQL Server 2000 DTS

Gerald_J_Rukavina

SSC Rookie

Points: 37
More actions
December 28, 2008 at 11:05 am

#195382

I am trying to read a text file in DTS. The file will be used as the source for a table. The text file is 4000 characters long. I am going through the properties of the file to set the fixed field column positions. I can not set a column passed character 3640. I can see the characters, but when I click to create the column, nothing happens. I have no problems creating columns befor position 3640, but can't create columns after.
Is there some kind of limitation on columns for source text files or is the something I am doing wrong?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Are all the records in the file 4000 characters long or is there one, or more, records that are only 3640 characters long?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Gerald_J_Rukavina SSC Rookie Points: 37 More actions · Answer 2

All of the records have a length of 4000. The file is coming from the mainframe.

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

Gerald

Instead of using DTS have you considered using BULK insert in a table tab1 with just one field fieldname type varchar or nvarchar.

When creat a table tab2 with all the fields derived from the textfile.

Use a insert statement like

Insert into tab2 select substring(fieldname,1,3), substring(fieldname,4,2) etc from tab1)

This is to my opion easier to mantain and don't forget DTS is not standard in SQL Server 2005 and 2008 when you upgrade.

/Gosta

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello again,

If you still want to use DTS and the data in the file is not sensitive, could you post a (small) sample file?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Gerald_J_Rukavina SSC Rookie Points: 37 More actions · Answer 5

Thanks for your ideas!

I am new to SQL Server and DTS. I may have to look into the bulk insert.

I can't post the file because it is sensitive data. It is just a straight text file. No packed decimal or anything like that. I ran into a problem with packed decimal previously and had to convert all the numeric data to text.