I have a variable length fixed-width text file. I am transferring this file from a mainframe via ftp using sdi32 within a DTS package.
The file has as it's last field a 1 char indicator. If this indicator is blank the file contains the crlf in the position of the last field instead of after the position of the last field. If this short record is the first record in the file then sql incorrectly determines this to be the end of the record position for all the records.
190002669565199706149910 C00000000000000000000000000000PPH 9920 TEMPORARY EMPLOYEES 01NT04200000950000000 Y00000000000VT19970815 NNNSO00000NRCY00100--End of record 1
190002669565199612279910 C00000000000000000000000000000PPH 9920 TEMPORARY EMPLOYEES 01NC04200000950000000 Y00000000000VT19970110 NNNAC00000NRCY00100N--End of record 2
As a work-around I use Access as a middleware translator because Access correctly determines the length of the record. I would like to get away from using Access as a translator.
I researched awhile ago and this was a documented problem from Microsoft.
My question is: Is there a fix for this or some other cleaner way to import this file?