Link to home
Start Free TrialLog in
Avatar of scooby_56
scooby_56Flag for United Kingdom of Great Britain and Northern Ireland

asked on

SSIS - styling excel destination

Hi Experts,

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 ?



OUTPUT
----------
Destination [2529]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0202025 at DFT ST Responses, Excel Destination [2529]: 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.

Avatar of slam69
slam69
Flag of United Kingdom of Great Britain and Northern Ireland image

in your ssis package turn on delay validation on the properties of that container
Apologies it would appear they have changed our profiles here in teh office and that posted as an admin comment

Avatar of scooby_56

ASKER

Thanks for the reply slam69.

I already have applied DelayValidation to the 'Data Flow Task' itself. Is there somewhere else I can apply the property?

It seems to work fine up unil the point i open the Excel file manually then save it. Even if i dont apply any styling and just open --> do nothing --> save.
ok once you have manually set teh sheet to be how you want it to appear save it as a template and then go back to your package, reopen the data flow task and double click teh destination container again, it may just need to refresh the column data mappings since they have been "amended" by your save action
Dont know if this helps but, one of the fields contains > 255 characters. If i open the template and add > 255 characters manually to this column then save it. The package executes succesfully. however the rubbish data is copied over from the template and remains in row 2 ??!!

I did this becuase I had read on another forum (sqlservercentral i think) someone tried this for a simialr error. he wasnt trying to do the same thing as me however.

When I save the file manually something seems to be happeining to the field types (or something). the action of placeing over 255 characters means at least one of the fields is corrected.

Ideas anyone ?
Anyone any Ideas?
Tried as you suggested. Pointed the Excel Destination to the Excel template file then re-selected the 'Name of the Excel sheet' in the drop down looked at mappings - seems okay...

Executed the package and still fails with same error output.
SOLUTION
Avatar of slam69
slam69
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I appreciate the help slam69

The requirement is for the package to output to 3 different sheets within the file. So the flat file destination with an Excel target is probably not an option. Worst case scenario I cant have any styling in my report which is a bit dissapointing as im sure others have managed this...

I would like to set up a few named ranges in the report also so I can output some metrics to a (kind of) summary sheet.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Not sure what you mean by this...
"...did you try adding a string to the column in question of over 255 and then resaving before trying to run teh package..."

sounds the same as i mentioned earlier...
"...If i open the template and add > 255 characters manually to this column then save it. The package executes succesfully. however the rubbish data is copied over from the template and remains in row 2..."

Would a TRUNCATE command work in this case? effecively clearing out the 255 random characters in row 2 before inserting data?
no but if you enter the string save and close it reopen it and delete out the row save and close and then try running package again see if it works, should remember the data type and allow the insert
Ah - got it..

Went back into the template file - removed the data from the column (didnt remove the row just the data) and saved.

package failed.

What i cant work out is why when i create the template via the Execute SQL task (Create Table) and leave well alone the meta data is correct  and all is well.
The connector to excel from ssis is flaky at the best of times and id always recommend using a flat file over excel destination any day of the week due to these types of issues and sepcifically with data type issues.

Its a good question you have and one id love to know the answer to myself unfortunately its not one I can answer for you... another scenario i have issues with is using excel files as source files for dts with leading zero's, when you use these and need teh leading zeros they will invariably get stripped off which again means more coding around the issue.

not sure if this would be an option for you but how about embedding a row at the top of the sheet with teh rubbish in which you then hide in the template so its now seen, you can set your named ranges etc in the template and shoudlnt cause any grouping issues?
Thanks again slam69 - I'm going to leave the question open for the moment in hope that someone has done this before and can offer an absolute solution.

I wonder if it would help if i could abstract away from my project and recreate a very simple example of the problem using Adventure Works and upload that package here?

Although the hidden range might work its not ideal - i think i would have to go with NOT applying styling or ranges to the report if i find I'm unable to find an acceptable (for the client) solution.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nobody else came up with a full solution so have awarded some points to slam69 for the assist.