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: Yesterday @ 9:39 AM
Points: 5,370, Visits: 9,017
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:42 AM
Points: 5,317, Visits: 12,357
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #977755
Posted Tuesday, August 31, 2010 7:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 1,510, Visits: 8,471

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 @ 9:39 AM
Points: 1,522, Visits: 2,733
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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 1,510, Visits: 8,471
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