Post reply

"Invalid Column Length" error

  • January 10, 2002 at 10:06 am

    #104701

    I'm currently using SQL 7.0 to load a fixed length text file into a table. The all the fields in the table are varchar and nullable. The table has the same design as the layout of the file.

    When I create the connection to the file I am able to map out the columns and everything looks fine. I then create a data pump to import the file into the table. When I attempt to setup the transformations I receive the following error:

    Error Source: Microsoft Data Transformation Services Flat File Rowset Provider

    Error Description: Invalid Column Length.

    We've had problems with this file from the beginning. The client sent the file with a null EOL marker. We are currently using the following vbscript to give the file an EOL that DTS will recognize.

    If fsoFile.FileExists(strFileName) Then

    Set tsoFile = fsoFile.OpenTextFile(strFileName, 1)

    Set tsoOutFile = fsoOutFile.OpenTextFile(strOutFilename, 2, True)

    Do

    strFileData = tsoFile.ReadLine

    strFileData = Replace(strFileData, Chr(0), " ")

    strFileData = Left(strFileData, 805)

    tsoOutFile.WriteLine strFileData

    Loop Until tsoFile.AtEndOfStream

    tsoOutFile.Close

    tsoFile.Close

    End If

    Any help would be greatly appreciated.

    Thanks,

    Jason

  • January 10, 2002 at 10:24 am

    #422058

    Wouldn't you want to replace chr(0) with chr(13)?

    Steve Jones

    steve@dkranch.net

  • January 10, 2002 at 10:38 am

    #422060

    Not the way I understand the problem. Chr(0) is the NULL EOL. I want to get rid of it. Then take the data in the line (which is 805 characters long) and write it to the new file. When you use the the file system object to write a file it places a chr(13) and Chr(10) at the end of the line.

    I was given this code by a VB developer who I'm doing this package for.

    Since I posted a few minutes ago, I've looked at his vbsript closer and found a logic error. The script we placing a blank trailer record at the end of the file. I moved the Read before the loop begins and after I write the line. This has gotten rid of the trailer record, but I'm still getting the error when I create the data pump.

    Thanks,

    Jason

  • February 11, 2002 at 8:58 am

    #423658

    Through the help of another developer, we found a solution. Believe it or not, this actually worked. We found that the DTS wizard ignored the Null EOL and went on reading the line in fine. This was great for the first file, but I had 3 more to load. I saved the wizard's package and then used the DTS designer to add the other 3 data pumps. All of this worked fine.

    If I created all 4 data pumps myself, I received the "Invalid Column Length". But one with the wizard and the other 3 myself works.

    I checked all the properties between the package that worked and the one that didn't. I counldn't find any differences.

    Does anyone know why with the wizard it worked but by hand it doesn't?

    Thanks,

    Jason

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply