SQL Server 2000 DTS - Text File Source

I am new to DTS and hope someone can help.

I have a Text file source that is used to read in records from a text file.

The records have fix length fields with a CR/LF at the end of each record.

The problem I have is that when I attempt to use the Data Pump to transform these records into a SQL table, I lose some of the records.

The reason appears to be due to the fact that the source file truncates the record if the last fields are blank, and the length is variable.

The Data Pump does not appear to like this and when a record of this type is found, the system appends the following record into the current record, causing a concatination of the records.

Any Ideas on how to solve this?

Thanks in advance

Can you modify the input data with a default value for the columns that are blank and then scrub the data, removing the default value once it's in the db?

Can you split the data once it's in the db?

One confusing point is the CR/LF. If there's one on the end of the short row why is the next row being concatentated? Are you certain that the CR/LF is truly the End of Row identifier?

I have validated that each row ends with a combination CR/LF and the Text File Source input connection recognizes this.

I then identify the fixed length fields. I have tried identifying all the fields, down to attempting each line as 1 field.

Regardless, when I attempt the transformation in DTS using the Transform Data Task, any row that is smaller than the assumed field size or identified end of row, continues reading the following line truncating it at the expected record length and appending it to the first line.

For example:

Assume a normal record length of 18, followed by a (CR/LF)

with the following data containing 3 rows, when I transform, I only get 2 rows in the database.

Original rows:

FIELD1FIELD2FIELD3
FIELD4FIELD5
FIELD7FIELD8FIELD9

Resulting Rows:

FIELD1FIELD2FIELD3
FIELD4FIELD5FIELD7

Any help is appreciated. :confused:

JStraw - if I can butt in here for a moment, I think your problem may be confusion between the terms FIXED and DELIMITED.

In a text file with DELIMITED fields, each data field is separated from the next by a particular character, usually a comma, tab, semicolon or one you specify. Additionally, each row of data is ended by a CR/LF pair or other marker.

In DELIMITED fields, the actual width of each field does not matter. In fact, null or empty fields can be indicated by two consecutive delimiters (such as A,,C - B is empty). Obviously, if the fields can be various widths, the entire row or line of data may a different length from the other rows. But, each row should contain every field or at least a delimiter for them.

In FIXED width fields, each field should ALWAYS be the same width from row to row and each row should ALWAYS have the same number of fields. If this is done, each row will be the same length.

Usually, without resorting to programming code of some sort, you cannot handle varying width fields that do not have delimiters between them. How would you know where the fields start and end in each record?

Does this help? Let me know if I've missed the point or confused your further.

RJ

You may want to pursue using the Bulk Insert Task with a format file. In a past life I was responsible for a system where data from a mainframe was loaded into SQL. The data did not contain delimiters. I had to define format files that described the data.

The data load was accomplished with BCP using .bat files. When the database was migrated to SQL2K, I just used the same .bat files.

I know the Bulk Insert Task has the capability to use an existing format file and has the ability to create one if one doesn't already exist. The problem is that I don't have working experience with this so I can't give you any insight.

Based on the information you've supplied, I feel confident that this is the path you'll have to take.

Hope this helps.