• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Text File and DTS

I'm new to using DTS so bear with me....

I have a database that I want to setup a DTS package to run weekly.  I am having a problem with the text file.

I am setting up the DTS initially by right clicking the database then choosing all tasks --> import data.  My data source is set to a  text file.  Here is how the file format is setup:

Format:  Fixed
File Type:  ANSI
Row Delimiter: {CR}{LF}

I then click next and here is where the problem starts.  In the text file the last column may have some blanks.  So the first 2 rows have the last column filled in but the third row there is no data so it's blank in the text file.  DTS is filling in the last column with data from the first column on the next row?  I try to fix it in the next screen by creating and moving the vertical lines that indicate the starting/ending positions of a column but the problem is that the last line DTS creates (it's the red line) and I am unable to delete or move this line.

Just wondering if there is someway to fix this so that DTS sees the data correctly.

  • 2
1 Solution
Fixed Type is a fixed line width file, so your format doesn't meet requirements for it. You can

Variant A:
1. Create DTS Transformation TXT1(String delimited)->TXT2, where you add missing last column in ActiveX.
2. Create DTS Transformation TXT2->SQL

Variant B:
1. Create DTS Transformation TXT1(String delimited)->SQL, where you calculate all columns in ActiveX. I thing it is too complicated for a beginner, but you can try.

What is TXT1(String delimited)? You used TXT1(Fixed) before, but it failed. So switch to Delimited Type and specify a delimiting string, which is not in data. It can be TAB, |,|||||,....
stuartr52Author Commented:
I've tried to interpet what you wrote but am having trouble with what you wrote in Variant A:

Not sure what you mean by  TXT1(string delimited) -> TXT2.  

Are you saying take the original text file and make a new one then create the DTS Transformation on the second file.

This makes sense but I don't know where I can add the missing column in ActiveX?
Define source file as varible string delimited file and use this ActiveX column transformation:


30..length of last column
300..maximum length of row

It creates a new column in an existing column and you write it without text delimiters into a target file. The new file if a fixed length file.
If the DTS is skipping the last field if it is blank, I would assume that the file is not proper fixed width. In a fixed with file, each record should be exactly the same length even if a field is blank.  If the records are all the same length, it would import blank ('') for the last field and move on to the next record and start at field one again.

You may wish to have a closer look at the file, it may be delimited by a TAB or Comma.  Also, make sure you have defined the field lengths in the import spec when using the DTS wizard, so it knows where each field starts and ends.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now