[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SSIS - styling excel destination

Posted on 2010-11-16
17
Medium Priority
?
1,185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 25

Expert Comment

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

Expert Comment

by:slam69
ID: 34144497
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
ID: 34144672
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 25

Expert Comment

by:slam69
ID: 34144699
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
ID: 34144875
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
ID: 34144970
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 2000 total points
ID: 34145049
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
ID: 34145392
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
 
LVL 25

Assisted Solution

by:slam69
slam69 earned 2000 total points
ID: 34145448
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
ID: 34146002
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
ID: 34146182
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
ID: 34146286
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
ID: 34146340
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
ID: 34149886
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
ID: 34270579
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
ID: 34299446
Nobody else came up with a full solution so have awarded some points to slam69 for the assist.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

650 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