May 8, 2020 at 8:02 am
Hi, I am using SSIS 2019 to export data from SQL query to Excel. The issue is that I need to put a total on the bottom row for certain columns. That is easy achievable. The problem I have is to get those very same Totals in the last column. I am attaching a file to show what it needs to look like. Any assistance would be appreciated.
May 8, 2020 at 2:17 pm
This was removed by the editor as SPAM
May 8, 2020 at 3:35 pm
If SSIS is your only option, then you are going to need either some 3rd party tool that allows for editing the Excel OR you are going to need to do a scripting step. With the scripting step, you can use C# or VB code and (if it is installed), ODBC Microsoft.ACE.OLEDB.12.0 to open the connection and manipulate the excel file.
If you can use other options, I think SSRS with a subscription to export to Excel would be much easier for this and give you the results you expect.
If you are comfortable with the scripting task, then it is not a bad way to go and to get you started, you can check out the microsoft documentation:
which shows you how to open the excel file and find the worksheets and named ranges which they call tables.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply