SSIS saving the excel formatting when loading data

Question

SSIS saving the excel formatting when loading data

Karina271

SSC Enthusiast

Points: 113
More actions
January 17, 2024 at 1:57 am

#4362924

Hello,
I need to automate the process of loading the data from sql server from proc to excel file on monthly basis (each month there's a new file and it's name is also dynamic depending on the month and year). There is VBA in excel, which gets data from proc, adds it horizontally and provides visual formatting.
Now I want to automate it will SSIS (I'm noob) and would like to get rid of that VBA proc (bc it needs to be called in another file to modify another file). If the data would be designed to load vertically instead of horizontally it would be probably easier to implement.
Is that possible to keep formatting (default table design with the filter option) somehow or set it up in SSIS in order not to use macro? Or to call macro separate file in SSIS process somehow?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

Welcome to the forum.

The horizontal / vertical stuff you are talking about is best done (if possible) in SQL, prior to the SSIS package being called. SSIS can easily export the results of executing a stored procedure, so your T-SQL can be as complex as it needs to be to get the desired format.

Formatting of exported data in Excel has always been a problem. There is a post here which may help you (I have not tried this):

https://learn.microsoft.com/en-us/answers/questions/899649/ssis-output-to-an-excel-template-does-not-seem-to

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Karina271 SSC Enthusiast Points: 113 More actions · Answer 2

Yes, horizontal stuff is done with sql like you're saying.

I've seen that post before posting, it doesn't help me..

One thing I'm not sure if worth trying is to use maybe python script to apply excel visual formatting?..

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

You mean, like this?

https://www.sqlshack.com/python-scripts-to-format-data-in-microsoft-excel/

Looks like it will work, but I haven't done it. If you do make it work, please post back here, as I'm sure others would be interested.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel