Text File and DTS

Posted on 2005-05-10
Last Modified: 2013-11-30
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.

Question by:stuartr52
    LVL 13

    Expert Comment

    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, |,|||||,....

    Author Comment

    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?
    LVL 13

    Expert Comment

    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.
    LVL 1

    Accepted Solution

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now