How To Return T-sql Query With Column Names As First Row

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

Solution 1:

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

Solution 2:

You could try UNION ALL:

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