Post reply

Row Order is not getting preserved

  • October 29, 2009 at 9:09 am

    #213057

    When writing a sql table data to a flat file using the SSIS control flow, sometimes the Row Order is not preserved.

    The SQL table datasource has the record count as the last row.

    When dumping this table to a file, sometimes the record count appears in the middle of the file which basically renders the file incorrect.

    This happens randomly and often is the case when the file is large. Is there a way to preserve the row order?

    Thank you all

  • October 29, 2009 at 9:12 am

    #1072522

    By definition, tables have no order. If you don't specify an ORDER BY, the order that the rows will be returned in is not defined and can change.

    Change your SSIS package to use a query as the source and put an ORDER BY in the query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 29, 2009 at 9:21 am

    #1072533

    I second Gail's note. without an ORDER BY, you cannot count on any particular order of rows.

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

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