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?

• JNK: Is that the only field out of the specified order?

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.

• Akomarek: Thank you Jon, That id correct that part of the output; however, now the flatfile still contains the incorrect mapping from the previous run. I get the following error now: Package Validation Error Error at Data Flow Task [SSIS.Pipeline]: input column "CLIENTID" (165) has lineage ID 20 that was not previously used in the Data Flow task. Error at Data Flow Task [SSIS.Pipeline]: "component "Flat File Destination" (154) failed validation and returned validation status "VS_NEEDSNEWMETADATA".
• Jon Seigel: @Akomarek: You should just have to no-op edit the Flat File Destination, so it can remap the columns as I mentioned.
• MISNole: 8 years later this helped me out greatly!