How to return T-SQL query with column names as first row

Question

I'm writing a SSIS package to output data from a SQL Server 2012 database to a .CSV file for a client. The requirement is that the first row be the column names. Below is the query I've written for the Source in the Data Flow Task. The problem is, it always returns the column names as the LAST row, not the first. Why? How do I achieve this?

DECLARE @Today AS DateTime= GETDATE()
DECLARE @NextPayrollDate AS DateTime

EXEC mobile.getNextPayrollDate @Today, @NextPayrollDate OUTPUT

;WITH LatestEligible (EmployeeID, LatestBillVerified) AS
(
    SELECT 
        EmployeeID, MAX(DateBillVerified) AS LatestBillVerified
    FROM 
        Inv_DataReimbursement
    GROUP BY 
        EmployeeID
)
SELECT
    'Edit Set' AS 'Edit Set', 
    'Employee No.' AS 'Employee No.'
FROM 
    LatestEligible

UNION

SELECT 
    NULL AS 'Edit Set',
    d.EmployeeID AS 'Employee No.'
FROM 
    LatestEligible d
INNER JOIN 
    Employee e ON d.EmployeeID = e.EmployeeID
INNER JOIN 
    Inv_DataReimbursement dr ON d.EmployeeID = dr.EmployeeID 
                             AND d.LatestBillVerified = dr.DateBillVerified
WHERE 
    (dr.MonthlyServiceEligible = 'true'
     OR (dr.MonthlyServiceEligible = 'false' 
         AND e.DateEnd IS NOT NULL 
         AND e.DateEnd > @NextPayrollDate))
    AND dr.ActualAmount > 0

score 2 · Answer 1 · answered Apr 19 '19 at 18:56

2

You could try UNION ALL:

SELECT
'Edit Set' AS 'Edit Set', 'Employee No.' AS 'Employee No.'
FROM LatestEligible
UNION ALL
SELECT DISTINCT
NULL AS 'Edit Set',
d.EmployeeID AS 'Employee No.'
FROM LatestEligible d
INNER JOIN Employee e
ON d.EmployeeID = e.EmployeeID
INNER JOIN Inv_DataReimbursement dr
ON d.EmployeeID = dr.EmployeeID AND d.LatestBillVerified = 
dr.DateBillVerified
WHERE (dr.MonthlyServiceEligible = 'true'
OR (dr.MonthlyServiceEligible = 'false' AND e.DateEnd IS NOT NULL AND 
e.DateEnd > @NextPayrollDate))
AND dr.ActualAmount > 0

answered Apr 19 '19 at 18:56

Lukasz Szozda

162,964
23
234
275

1

That's it! I had tried UNION ALL, but what did it was adding DISTINCT to both SELECT statements, actually. Done and done. Thanks so much! – Melanie Apr 19 '19 at 19:00
This doesn't guarantee order and is not a good way of achieving their stated objective – Martin Smith Apr 19 '19 at 19:44

score 2 · Accepted Answer · answered Apr 19 '19 at 19:43

2

How do I achieve this?

Don't do it through SQL.

Just tick the Column names in the first data row box in the Flat File Connection Manager and leave the original query untouched.

The column headers will then be added automatically without you needing to union this additional metadata (and potentially cast everything as a string on the SQL side).

answered Apr 19 '19 at 19:43

Martin Smith

438,706
87
741
845

When I do this, I don't get the column names I assign in my SQL Statement in the OLE DB Data Source; I get the names that SSIS assigns randomly to the columns in my spreadsheet. The spreadsheet is initially blank - no column names. – Melanie Apr 22 '19 at 15:37
OK, I think I got it. If I create a csv file with column names ONLY and then link to a Flat File Connection Manager with Column names in the first data row checked, it works! Thanks! – Melanie Apr 22 '19 at 18:16

How to return T-SQL query with column names as first row

2 Answers2

Linked