SSIS -> Excel Formatting Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SSIS -> Excel Formatting Issue

SSIS -> Excel Formatting Issue

Rate Topic

Display Mode

Topic Options

Author

Message

Jako de Wet

Posted Thursday, February 14, 2013 1:23 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:25 AM
Points: 85, Visits: 137

Hi everyone

What is the best way to keep the formatting in Excel? I have created a template with the font, column types, graphs, sums etc and removed all the data.

When I export the data into the columns, the formatting is gone, font size is different and the SUM/Graphs does't auto update as I thought it would have.

Is there any easy way around this?

Most of the posts Ive read about this, talks about using a Script Task to do further formatting?

Thanks

Post #1419858

nick.ryan 5248

Posted Tuesday, February 19, 2013 8:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 07, 2013 7:18 PM
Points: 4, Visits: 1

I had to do this recently and my workaround was to have 1 row of formatted data retained under the column headings in my template but hidden.

However, that was a simple table of data without graphs and formulae. It did cause the correct formatting and data types to be applied to all lines inserted so it may work for you.

Post #1421915

joeroshan

Posted Tuesday, February 19, 2013 8:41 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 573, Visits: 1,165

In addition to nicks suggestion, you can use script task as per the below link

http://stackoverflow.com/questions/1954582/ssis-2008-excel-2007-formatting-excel-columns-properly-exporting-not-importin

I have not used this , but will keep this in mind so may be useful one day

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups

Post #1421918

Jako de Wet

Posted Tuesday, February 19, 2013 11:27 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 11, 2013 2:25 AM
Points: 85, Visits: 137

Hi everyone

Just to let you know what I did.

I created a XLS template with the headers and a hidden/formatted row, this just formats the actual numbers etc. You still need to apply the font-size via a script. The formulas also seem to auto run.

So I created a C# Script file that adds all the formatting and formulas afterwards. Works really well and takes out human intervention.

Only problem is that, that was the easiest report of the 25+ I need to do...

Post #1421935

« Prev Topic | Next Topic »

Permissions