Once again it looks like Excel is the poor relation in SSIS.
I am trying to create a DataFlow task which connects to my database and runs an SQL SELECT statement into an Excel results set.
The SQL statement is as follows
coalesce(F.SpecialNeedsDescription,'') as 'SpecialNeedsDescription',
coalesce(count(CDC.complaintref),0) + coalesce(count(CMS.complaintref),0) as 'TotComplaints',
coalesce(count(CDC2.complaintref),0) + coalesce(count(CMS2.complaintref),0) as 'TotOpenComplaints',
coalesce(count(CDC3.complaintref),0) + coalesce(count(CMS3.complaintref),0) as 'TotOpenFSAComplaints'
Segments S on S.cppolref = OBR.cppolref
AND S.validfrom = OBR.Validfrom
cpfact F on F.cppolref = OBR.cppolref
AND F.validFrom = OBR.ValidFrom
priceFact PF on PF.cppolref = OBR.cppolref
AND PF.ValidFrom = OBR.ValidFrom
CDCComplaints CDC on CDC.cppolref = OBR.cppolref
AND CDC.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
CMSComplaints CMS on CMS.cppolref = OBR.cppolref
CDCComplaints CDC2 on CDC2.cppolref = OBR.cppolref
AND CDC2.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
AND CDC2.Status = 'Open'
CMSComplaints CMS2 on CMS2.cppolref = OBR.cppolref
AND CMS2.status in ('open','FSA open')
CDCComplaints CDC3 on CDC3.cppolref = OBR.cppolref
AND CDC3.ValidFrom = (SELECT Max(ValidFrom) from CDCComplaints Where Validto <= f.validto)
AND CDC3.Status = 'Open'
AND CDC3.FSAReportable = 1
CMSComplaints CMS3 on CMS3.cppolref = OBR.cppolref
AND CMS3.status = 'FSA open'
OBR.BatchID = 1001
Firstly, SSIS seems to choke on the colaesce() statement; when I put the code into Build Query it puts the coalesce in square brackets [coalesce] and treats it as a field name which then breaks the syntax - WHY!!!!!!
I have replaced the coalesce() with isnull() and it seems to be happier, but still gives me an error message "syntax error in FROM clause" when I try to parse the query. Both versions of the query work just fine in Mgmt studio