SSIS - How to Set Output column width to varchar(max)

  • isuckatsql

    SSChampion

    Points: 10381

    I have an SSIS project that works fine on files smaller than 8k.

    When i use a file larger than 8k, it fails due to a truncation error.

    I have tracked the issue to the 'set column width' which is limited to 8000.

    How do i change the 'set column width' field to varchar(max) and get around my issue ?

    BTW some of my input files are 35k.

    Thanks

  • isuckatsql

    SSChampion

    Points: 10381

    I have searched the internet for a solution, but no luck.

    This seems very easy, but for some reason poorly documented.

    Has anyone else experienced this ? What is a workaround solution ?

    Thanks

  • JamieX

    Hall of Fame

    Points: 3733

    Since varchar(max) replaces text data type, does changing the input column type to text work?

  • Lowell

    SSC Guru

    Points: 323331

    behind the scenes, the length is set to -1 for the column in syscolumns. can you set the width to -1 instead of 8000?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • isuckatsql

    SSChampion

    Points: 10381

    Changing to -1 did not work, but changing to text format worked!

    Many thanks for your assistance 😀

  • theobellash

    Newbie

    Points: 7

    You can put 8000 since it is the value that cannot be reached by VARCHAR(MAX)

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

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