Post reply

Buffer increase with sort transformation

  • August 9, 2006 at 9:29 am

    #116046

    Hello all,

    I have a package which load 30.000.000 lines from a table sqlServer2005 and write in another table SqlServer with some transformations.

    I have a SqlServer destination and the package works fine, the destination table is filled all the 10.000 lines.

    Now when i put a sort object, the buffer increase and the destination is never filled...

    Finally i have a timeout (i can change the timeout but i think the server failed if i load 8GB of data...).

    May be i can sort at the enter but i want to know if there is a way to sort and insert by bath in sql2005 with ssis.

    Thanks very much.

     

  • August 10, 2006 at 4:33 am

    #654251

    Do you need to sort? you are loading data into the a table - it should not need sorting - sorting would be done when retrieving data from that target table. Also, if you have some requirement that really require the data to be sorted, what about defining that sort order, and then creating a clustering index on the target table, based on that order? Then SQL will place the data, per the clustering key, in order on the table.

  • August 10, 2006 at 11:37 pm

    #654476

    If you are working with that much data, you might find that Nsort works faster

    http://www.ordinal.com/ssis.html

    Julian Kuiters
    juliankuiters.id.au

  • August 11, 2006 at 1:40 am

    #654483

    I think you are right, i want my destination table sorted so the clustering index is what i need.

    Thanks a lot Wanderer

  • August 11, 2006 at 3:24 am

    #654496

    glad I could help 🙂

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

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