How to Insert Fixed Date into Excel Header

Transact-SQL
1

Hi experts,
This relates to SSIS exporting to Excel. The SSIS forum does not get much activity so I'm posting here.
I simply want my excel sheet to reflect the date it was populated. Not the current date the user is looking at it.
For example, is it possible to format the heading line as "My Report - 01/10/2023" ?
Better yet, is it possible to read the date from a table and insert that date value into the heading?

Thanks for any ideas.

2

I know I've mentioned it a few times, and I think others have too, but anything beyond outputting rows to Excel using SSIS will need to use a custom script task that modifies the generated Excel file. Alternately you can run some PowerShell outside of your SSIS package to do that.

Or just add a ReportGenerationData column with the current date and time.

2 Likes
3

I think it has been mentioned before - but just in case it hasn't:

If you want formatted Excel reports your best option is to use SSRS to design the report and export the report as Excel. This gives you all of the abilities you are looking for - where you can control all aspects of the formatting.

Additionally, you can also export the report in other formats - such as PDF, Word, CSV or even use MHTML (embeds the report in the email).

Trying to manage this using SSIS takes a lot more time and effort and is often not able to accomplish the desired outcome without resorting to script tasks and manual manipulation of the file(s).

2 Likes
4

Thank you @jeffw8713 and @robert_volk. Yes it has been mentioned before but I was hoping for an easy workaround. a ReportGenerationData column might give me the current date the user was looking at the report or would it not? I want to place a date from the table record into the heading. Thanks again. I've had good success wit h SRS so I'll develop an SSRS report.

5

Not sure why you expected a different answer :laughing:

Excel has its place but not at my work place. We have gotten bitten with such a tool that users can fudge the numbers in. Also the data within it is stale the minute it is exported. 0 validation.

I think on many occasions you have been encouraged to leverage SSRS. Best way to go

6

SSRS is the easy workaround :slight_smile:

2 Likes