April 19, 2019 at 6:54 pm
I'm writing an SSIS package to output data from a SQL Server Database (2012) to a CSV file for a client and 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
April 19, 2019 at 7:07 pm
Why are you trying to add the header in SQL? There's an option in SSIS in the flat file connection to add a header row with column names.
April 22, 2019 at 3:41 pm
When I do that, I don't get the column names I assign in my SQL statement in the OLE DB Data Source; I get column names that the SSIS package randomly assigns in the Flat File Destination (Column1, Column2, etc.)
April 22, 2019 at 6:16 pm
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply