Solved

SSIS - styling excel destination

Posted on 2010-11-16
17
1,146 Views
Last Modified: 2012-05-10
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.

0
Comment
Question by:scooby_56
  • 9
  • 7
17 Comments
 
LVL 25

Expert Comment

by:slam69
Comment Utility
in your ssis package turn on delay validation on the properties of that container
0
 
LVL 25

Expert Comment

by:slam69
Comment Utility
Apologies it would appear they have changed our profiles here in teh office and that posted as an admin comment

0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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.
0
 
LVL 25

Expert Comment

by:slam69
Comment Utility
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
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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?
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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.
0
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 500 total points
Comment Utility
ah there you go that will be your issue, have seen before that ssis hits snags when exporting to excel strings>255.

try this,

change your destination to a flat file but point it at the excel file not a cvs (may need to type pathway manually) this will likely get the information into your sheet but probably wont hit the right worksheet.

Worth testing for now, if you need it as you have it though then the 255 length IS going to cause an issue, ways I have seen round it are truncating it or splitting it through a conversion task at the 254 character.

I have read that if you add data to teh second row in the column your having issues with and make the length of teh cell over 255 characters by adding text, then saving and trying to readd you may get some luck, not something I have tried but worth giving a go
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 25

Assisted Solution

by:slam69
slam69 earned 500 total points
Comment Utility
thats the problem you are going to have as ssis wont do the manipulation you need within the excel work book.

I have formatting set up on an excel template file that this does work for, but i dont have the >255 charcter issues, did you try adding a string to the column in question of over 255 and then resaving before trying to run teh package as a couple of posts I have read do say thsi will work?

if not id be inclined to do the data dump to the excel file as it works and then write a macro to do your formatting post. you can use a script task within teh ssis package to run the macro?
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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?
0
 
LVL 25

Expert Comment

by:slam69
Comment Utility
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
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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.
0
 
LVL 25

Expert Comment

by:slam69
Comment Utility
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?
0
 
LVL 12

Author Comment

by:scooby_56
Comment Utility
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.
0
 
LVL 12

Accepted Solution

by:
scooby_56 earned 0 total points
Comment Utility
I had to compromise in the end.

I created a template excel file that had just one sheet, styled however I liked - this is where inserted some summary information about the report - name, time-range, time to compile, etc etc.

When the package executes, it takes a copy of the template file and then adds a worksheet at this point which enables me to specify column types for the second sheet and thus allows over 255 chars. The compromise was that i was unable to apply any style to the second sheet.
0
 
LVL 12

Author Closing Comment

by:scooby_56
Comment Utility
Nobody else came up with a full solution so have awarded some points to slam69 for the assist.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now