SSIS - styling excel destination
Posted on 2010-11-16
I'm trying to export a set of data from an OLE DB Sorce to an Excel Destination.
This package is executed within a schedule - In my Control flow I use a File System Task to make a copy of an Excel file and gives appropriate name etc.and populates the copy with data sourced from a stored procdure. So far so good - works fine.
i would like to style my Excel template with couloured headers ect... so.. I have manually opened the template file with MS Excel 2007 and changed the colour of row 1 then saved it as 97-2003 compatible.
Now when my package runs, it creates a copy of the template fine, but fails at the point of writing out the data. error bottom.
The template was originally created by executing a SQL Task (independantly of the rest of the control flow) using the Create Table statement and ConnectionType=EXCEL.
What happens to the file when i manually edit it which prevents me from writing to it?
how does everyone else style their Excel reports ?
Destination : An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at DFT ST Responses, Excel Destination : Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at DFT ST Responses, DTS.Pipeline: component "Excel Destination" (2529) failed the pre-execute phase and returned error code 0xC0202025.