Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Is that the only field out of the specified order? –  JNK Oct 22 '12 at 16:56

1 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.

share|improve this answer
    
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". –  Akomarek Oct 22 '12 at 18:16
    
@Akomarek Please do not post comments as answers. If you need to reply to a posted answer, click Add Comment below the post to add your comment. –  JNK Oct 22 '12 at 18:23
1  
@Akomarek: You should just have to no-op edit the Flat File Destination, so it can remap the columns as I mentioned. –  Jon Seigel Oct 22 '12 at 18:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.