SQL column goes from first to last position in output

Question

I have an SQL query inside a SQL Server Integration Services (SSIS) package where the preview shows the column order correctly, however the actual output uses a different column order.

This is exported to a .csv file for import into another application and needs to be in a specific order.

I need the Date column first, which shows up that way when I preview the data output in the OLE DB Source Editor window; however, when I click on the "Columns" to display their order it shows them outputing in the last position, which is what they do.

Here is the SQL query:

DECLARE @now DATETIME;

SET @now = GETDATE()-45;

SELECT CONVERT(VARCHAR(8), [ORDERS].[STARDAT], 10) AS [MM-DD-YY], [ORDERS].[CLIENTID], 
    [ORDERS].[SAMPNAME], [ANALYTES].[OP10AN], [RESULTS].[FINAL]
FROM [ORDERS], [RESULTS], [RUNS], [ANALYTES], [SamNam]
WHERE [ORDERS].[sampname] = [SamNam].[SName]
    AND [RUNS].[RUNSTS]= 'D' 
    AND [RUNS].[COMPDATE]>@now 
    AND [ORDERS].[SAMPTYPE] IS NULL 
    AND [ORDERS].[ORDNO] NOT IN (308333,308334)  
    AND [ORDERS].[CLIENTID] <> 'Ind Waste' 
    AND [RESULTS].[ORDNO]=[ORDERS].[ORDNO] 
    AND [RESULTS].[RUNNO]=[RUNS].[RUNNO] 
    AND [RESULTS].[ANALPRINT]= 'Y' 
    AND [ANALYTES].[SINONYM] =[RESULTS].[SINONYM] 
    AND [ANALYTES].[TESTCODE] =[RESULTS].[TESTCODE] 
    AND [ANALYTES].[OP10AN] IS NOT NULL
ORDER BY  [ORDERS].[ordno];

Any input into why the query is getting turned around?

Answer 1

In the Columns page on the OLE DB Source, you can use the check boxes beside the columns to change the order.

The columns are added to the output in the order in which they get checked, so what you need to do is uncheck all the columns, and then check them again in the correct order.

Downstream data flow tasks will want to update themselves because the column mappings "broke", but this shouldn't be a big issue to fix.