question

JermaineB avatar image
JermaineB asked

Excel Formatting Utilizing SSIS

Is it possible to format individual cells within an excel file? If yes then what are all possible ways to accomplish this?
ssisexcel
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
It depends what formatting you need and how should loook the output. In general there are 3 possibilities. 1. Prepare a formatted template. Then SSIS can copy that template into destination and write data into it. 2. Write data into excel firle directly. THen in next step a SSIS Task can open the excel and manipulate the formatting of individual cells using VB.NET or C#. (In this case you will need excel installed on th emachine on which SSIS is running. 3. Prepare a excel template without formatting but which will contain macro, which can do whatever formatting necessary. Then SSIS again copies such excel into destinetion and writes data. Then there are trwo possibilities - execute the macro from withing SSIS Script Task (again ecesll installation is necessary) or make the macro auto start and then the formatting will be done when user opens the excel. If the formatting doesn't need to be something special, than I suggest to go by option 1, which is probably the easiest one, but it really depens on the formatting needs. Another option (some times can be better) is to use SSRS instead of SSIS for producing excel output. You simply develop a report which will contain whatever formatting necesary and then you nca create a subscription to that report which will automatically generate the excel files. In SSRS you have much better possibilities of formatting. If the data needs to be prepared by some complex ETL logic, then the SSIS and SSRS can be combined to produce perfect output.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ooops....I didnt refresh my browser before posting my answer ;)
0 Likes 0 ·
Sacred Jewel avatar image
Sacred Jewel answered
SSIS is not the right place to do it. You will face all kind of problems even after installing some client software (particularly Excel interop). So the best you can do is to have a template file and add a macro in it which will format the sheet. But if you want to continue let say Excel interop path, then SSIS script task may help you.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.