Solved

SSIS - styling excel destination

Posted on 2010-11-16
17
1,169 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 500 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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

691 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