SSIS package to export query result to CSV does not export all the records

Question

I am developing on Visual studio 2015 (with data tools extensions) a simple SSIS package: it has a connection (Sql server 2008), an OLEDB Origin, containing a query, connected to a flat file destination.

• The db connection is tested an it works;
• The oleDb origin works, there are no variables in the query;
• The query inside the OleDb origin returns about 700 records
• The preview in the OleDb returns 200 records (as configured)
• The mapping between query and file is 1:1, all the fields from query are included in the resulting csv in the same order.

When I run the package the csv file is created, it contains exactly the needed fields but only 169 records (expected about 700).

I compared the contents of the csv with the data from the query and noticed that:

• Records are not in the same order
• The records not exported are seemingly random placed in the sql results (I supposed that the exported were the first ones but it is not the case)
• The data in the missing records has nothing strange (no long string or null values, or out of range values)
• Repeating the execution several times the extracted and missing records are exactly the same, in the same order.

Do you have any suggestion to solve this? I need to export all records in the same order. It seems that the Order by clause in the query is ignored by the export and that records exported are a sample of the original ones.

Answer 1

I discovered the issue, my fault.

I checked and saved the data from sql management studio, then developed the dtsx.

Meanwhile that data (that I believed to be unmodifiable) has been modified, therefore the dtsx showed the updated version and I checked it against the old version.