Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to create an user friendly excel file via SSIS? Expand / Collapse
Author
Message
Posted Monday, August 30, 2010 6:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 10:43 PM
Points: 6, Visits: 54
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,
Post #977652
Posted Monday, August 30, 2010 7:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:55 PM
Points: 6,582, Visits: 8,860
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 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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #977662
Posted Monday, August 30, 2010 8:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 10:43 PM
Points: 6, Visits: 54
Hi WayneS,
Thanks a lot for you response.

Cheers,
Fateme
Post #977663
Posted Tuesday, August 31, 2010 2:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #977755
Posted Tuesday, August 31, 2010 7:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:44 PM
Points: 3,105, Visits: 7,817

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

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.
Post #978004
Posted Wednesday, September 1, 2010 6:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 10:43 PM
Points: 6, Visits: 54
Thanks a lot Phil and Alvin,
Both solutions will be usefull.

Regards,
Fateme
Post #979233
Posted Thursday, September 2, 2010 6:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:42 AM
Points: 1,502, Visits: 2,669
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.
Post #979465
Posted Thursday, September 2, 2010 7:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:44 PM
Points: 3,105, Visits: 7,817
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

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.
Post #979495
Posted Wednesday, September 15, 2010 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2013 12:03 AM
Points: 125, Visits: 408
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
Post #986223
Posted Wednesday, September 15, 2010 6:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 17, 2010 10:43 PM
Points: 6, Visits: 54
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
Post #986826
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse