I'm looking for advice for the following 'issue'.
I need to send Excel files (not CSV) to our customers with SQL SERVER.
After intense searching, I've found out that the .XLSX extension out of SQL server isn't possible (not directly), and I should be using SSIS for this.
I've never used SSIS before, did some research, and found out that I need to create .dtsx packages and schedule them to run in SQL server.
I have up to 80 reports to be sent on a daily basis (each with individual queries, connecting to the same database); this database is a Linked server on the SQL server (Old school firebird DB).
Some of the queries result in multiple mails (e.g. "Send Mail for each record" - the record contains the receiver and it's values for the body of the mail). Others are just simple Xxcel tables.
Just to be sure that I'm on the right track here, these would be the steps:
Create one project in VS 2017 (Integration Services Project) for all the reporting
Create new Package (for each report)
Create ODBC connection to the SQL database (Data Flow => ODBC Source)
And this is where I'm stuck.
So some advice here would be great.
4) Send mail task Specify the attachment as the file you just created.
Personally, I prefer sp_send_dbmail as my method for sending email but either will work and both support attachments:
A third option is to use SSRS to build the reports as a table/matrix and then define a subscription for the users