Post reply

SSIS saving the excel formatting when loading data

  • 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?

  • January 17, 2024 at 9:49 am

    #4363168

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 17, 2024 at 2:53 pm

    #4363305

    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?..

  • January 17, 2024 at 4:55 pm

    #4363366

    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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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