Import Data using DTS

[arst06d]

We have been using MS Access to process downloads from the AS400 for MI purposes for years. Now migrating to SQLServer.

Trying to import the download files which are supposedly fixed length text files, with CR+LF as record delimeter.

I notice that although DTS asks fro the record delimeter it ignores it: say record 1 is 1000 characters + CRLF, and record 2 is 980 characters + CRLF, then the first 20 characters of record 3 are added to record 2.

I know the problem is in the download not padding out the data to the correct number of characters, but I find it odd that Access (all the way back to v2) recognises the CR+LF as end of record, but DTS does not, even though specified.

or am I missing something?

 

[fluteplr]

Did you define the file type as fixed length? If so don't.

 

[arst06d]

If I dont define it as fixed length (which it should be) then I have to specify record delimeters, of which there are none.
Again, it's all down to the download file not being padded out by the AS400 routine, but it seems a strange that lowly Access recognises the problem while DTS does not.

 

[tlbroadbent]

When a file is declared as fixed-length DTS expects all rows to be the same length. I don't know why DTS doesn't handle these files in the same way as Access.

Darren Green has created a custom DTS task to pad text files. It may or may not meet your need. Read about it and download if desired from the following link.

http://www.databasejournal.com/features/mssql/article.php/1462341

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

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