July 1, 2003 at 3:33 pm
I have an ASCII fixed-length text file that I'm having trouble trying to move into SQL Server 2000 through DTS. It's a file that I FTP from our mainframe MVS system. I've already written the DTS to periodically FTP this file and move it into our SQL Server. The only problem is that this flat file sometimes doesn't contain data in the last few fields on the first couple records. This throws the DTS package off completely and only imports every other record for some reason.
Fix-Length ASCII File Details
1. Records are fixed-lenth with {CR}{LF} as record delimiter. Basically, each record is on it's own line.
2. Total record length is 447 characters
3. The last few fields (byte #430-447) in this 447 record length may contain spaces which represents no data for these fields.
Here is my problem. When creating the Text File (Source) object in the DTS Designer, I'm unable to chop it into it's 447 fixed length size. The GUI automatically ends at 430 because the first record in this file contains spaces from 431-447. This will happen the majority of time due to the way the data is stored.
I had to manually type in junk data such as "XXXXXX" to fill in the gap from 431-447 for only the first record in this file so that I can chop up the fields correctly. I was able to see and access these additional fields represented as ColXXX during my ActiveX Transformation script.
The DTS package successfully imported 100% of the records when the first record had data such as "XXX..." from 431-447. However, in production, the first record usually doesn't have data from 431-447. Hence, only 50% of the records are imported successfully. I'm a bit puzzled by this.
BTW, this text file is about 300MB. It's not possible for me to manually go in and add "XXX" everytime this DTS package is supposed to run. The DTS package is supposed to run Mon-Fri and should be completely automated. I hate to be manually typing in "XXX..." everytime in order for the DTS package to work properly.
I can't believe that the GUI interface won't let you define fields from 431-447 when there are no data in the first record. It just completely ends at 430 with that Red Line when you are chopping it up.
Does anyone know a way to overcome this limitation? Thanks.
July 2, 2003 at 6:57 am
I'm using SQL7 SP4 and don't have any trouble defining or importing fixed length text files with or without trailing spaces in the first record. Are you sure your records are all the same length and contain trailing spaces.
When defining the input file use a test file with one record of the exact length with no spaces and use that to define the input then try loading your actual data.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply