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: 95460 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: 246960 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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