How to create an user friendly excel file via SSIS?

Question

How to create an user friendly excel file via SSIS?

fateme.heidari

SSC Journeyman

Points: 88
More actions
August 30, 2010 at 6:23 pm

#140399

Hi everyone,
I have an ssis package that creates 4 data sheets in a excel file, the data are comming from some tables in sql server
When I create a excel file, I send that file to some email address.
But my problem is that the excel sheets that are created, are not very user friendly, and I need the headings be shaded, wrapped text, wide enough for the cell contents etc.
Is there any solution for above requirement.
I'll appreciate for any help.
Regards,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95409 More actions · Answer 1

SSIS will not do this automatically.

However, you can use a vb.net script task, excel automation, and set it all yourself. However, this would imply that you are running excel on a server, and this is not supported.

This article: Automating Excel from SQL Server[/url] might be helpful in figuring out how to utilize Excel Automation. It's designed around using T-SQL, not vb.net, but it should be easily adapted.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

fateme.heidari SSC Journeyman Points: 88 More actions · Answer 2

Hi WayneS,

Thanks a lot for you response.

Cheers,

Fateme

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Or you could embed some code in an Excel spreadsheet and (maybe) use this blank worksheet as a template.

The SSIS process populates the spreadsheet template with data and when the user opens it they can click on the 'Format' button and the pre-written code executes to tidy things up as required. Or you could make it auto-execute.

The benefit of this approach is the avoidance of the need to install Excel components on your SSIS server.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Another option is to use a Script Task to format the spreadsheet after the data has been written. This option should probably be left to the experienced programmer. I do not have the time to explain how to do it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

fateme.heidari SSC Journeyman Points: 88 More actions · Answer 5

Thanks a lot Phil and Alvin,

Both solutions will be usefull.

Regards,

Fateme

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

Just an idea, but you could use SSIS just to prep the data into staging table(s) and then use SSRS to create a formatted report & create a timed subscription to deliver the Excel file via email.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 7

WILLIAM MITCHELL (9/2/2010)
Just an idea, but you could use SSIS just to prep the data into staging table(s) and then use SSRS to create a formatted report & create a timed subscription to deliver the Excel file via email.

That should be easier than my solution.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

vani_r14 SSCrazy Points: 2843 More actions · Answer 8

Hi there

You could create a template excel wkbook and store it in a template folder... Each time before the package runs - copy the file over to the working folder get the data imported and emailed... LAter on if needed you could rename these files and store it in archive folder...

I recently had to do this and have done it this way... i have documented the process of copying and renaming files and creating excel sheets dynamically and renaming it... i can send them to you if you would like to use them

Cheers

Vani

fateme.heidari SSC Journeyman Points: 88 More actions · Answer 9

Thanks alot Ten,

I am new in SSRS but I am very eager to know more about that, so I think it's good opportunity to learn more detail about SSRS.

And thanks alot to Vani, could you please send me your solution, because I've done that but I got some issue with column that has numeric value.

I mean sometimes the header format will be copy to all of column's cells.

I'll appreciate for your help.

Regards,

Fateme

Naveen J V SSC-Addicted Points: 446 More actions · Answer 10

Hi,

I am facing similar issues. Need to export from SQL table to pre-formated excel sheet.

can u please help me by sending the steps. I am a newbie to SSIS.

Thanks in advance

Naveen