Records from internal Table not output in order to flat file

Hot Topic

'A required privilege is not held by the client' when re-initalization
automate GACing assembly
Managing PK/FK Relationships with the Tools
Union All
'Dynamic Time(year,month,date)' Report Template (rdl) using Report Designer VS2005
combine pdf with reporting services pdfs
@!#$%^&*( SSL/TLS
DataGridView/SQL Express - Updating record using seperate form
SQL 2005 Express slow/stalling
Range function on role-playing dimensions

SQL Server

Merge Replication with SQL Mobile 2005
Managing transactional replication
Error when using 'LIKE'
Problem with the INFORMATION_SCHEMA.COLUMNS column
Creating Additional Instances
How to determine if a database is in use
Save query results for Word Mail merge
The cursor does not include the table being modified or the table is not updatable through the cursor
SUM the return value of a EXEC SP
Renaming a File to include Date and Time

Records from internal Table not output in order to flat file

Hi -

I have a SSIS package that dumps data from an internal table to a flat file output using standard data flow tasks. The entire table is output - no special SQL. Most of the time the records are placed in the output file in the same order as the internal DB table, but occasionally the order appears to be more random. When that happens, the record order in the internal table is correct - it is just the output.

I can find no properties that seem to affect this. I would appreciate any hints and advice that anyone can give me. Has anyone else encountered this same problem

Thanks in advance,

Jim Sweeney

Answer this question

Records from internal Table not output in order to flat file

  • tongkusat

    Jim SweeneySLC wrote:

    Hi -

    I have a SSIS package that dumps data from an internal table to a flat file output using standard data flow tasks. The entire table is output - no special SQL. Most of the time the records are placed in the output file in the same order as the internal DB table, but occasionally the order appears to be more random. When that happens, the record order in the internal table is correct - it is just the output.

    I can find no properties that seem to affect this. I would appreciate any hints and advice that anyone can give me. Has anyone else encountered this same problem

    Thanks in advance,

    Jim Sweeney

    The record order would only happen if you had some asynchronous components in your data-flow. What components are you using

    -Jamie



  • Recep TARAKÇIOĞLU

    Hi Jamie -

    The Data Flow only has a OLE DB Source task and a Flat File Destination task. The source uses Table data access mode and passes on 3 of 4 available columns ( I do not pass on a autonum identify field). The selected source table always has the records in correct order when this problem occurs. I cannot see any properties to change that might affect this.

    Thanks in advance,

    Jim Sweeney

  • doveshack

    If you want to guarantee the order of your output you have 2 options:

    • Add an ORDER BY clause to the query in your OLE DB source (for that you have to change the to SQL Command or SQL Command from variable which is remonneded overTable or view mode) or
    • Add a SORT or any other Synchronous component to your data flow

    Rafael Salas

  • Will Merydith

    Rafael -

    I am going to change the OLE DB source task to use SQL and order by the autoNum column. It does seem like this should enforce the order, so hopefully the problem will go away with this approach.

    Thanks,

    Jim Sweeney
  • Records from internal Table not output in order to flat file
Answer this question