Column in pipe delimited file is being cut off importing into sql server 2005 via SSIS

  • ekampel

    Valued Member

    Points: 74

    I have a .txt file that is Pipe delimited with " as text qualifier.

    When I import it into Sql Server 2005 using an SSIS data flow task, it imports all of the rows, but one of the columns gets truncated to 30 characters or less.

    This column in the original data and file is up to 3500 characters in length.

    The destination table has the column declared as varchar(max).

    Does anyone have a clue as to why it would chop it off like this.

  • Jack Corbett

    SSC Guru

    Points: 184381

    On your task that imports the file have you checked the column length for this column in advanced settings input and output columns? My guess is that in your first row this column has a length of 30 and SSIS is defining the column to be that length.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • ekampel

    Valued Member

    Points: 74

    under advanced settings on the Flat File Connection Manager for the column productDescription, the datatype is set to text stream(DT_TEXT)

  • Jack Corbett

    SSC Guru

    Points: 184381

    Okay, so now check all the input/output columns in the transforms along the path. At some point there has to be a spot where the column is set to a length of 30.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • ekampel

    Valued Member

    Points: 74

    Under advanced editor for flat file source > input and output parameters :

    The flat file source output for my column productdescription is set to Text Stream for External columns and the Output columns section.

    I have been wasting hours trying to figure this out, seems like a bug somewhere.

  • ekampel

    Valued Member

    Points: 74

    I figured it out. The import was running fine.

    There was some sql code executed later in the package that updated the column to remove " signs.

    It was casting to varchar instead of varchar(max) causing a truncation to occur for some reason.

Viewing 6 posts - 1 through 6 (of 6 total)

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