SSIS – Create Excel Data File (1)

Sometimes, our business users prefer receiving Excel data files as attachment in emails (reports on the reporting server are nice, but who has time to go there).

Here is the overall design flow in SSIS to create data files in Excel format, save it onto a shared drive, and send to users via email as attachment.

Step 1: Delete any existing Excel data file first using a File System Task with Delete File operation.

The SourceConnection is defined in this File Connection Manager.

Step 2: Create the first sheet for the Excel file, the Summary sheet (it’s actually a table).

The Connection is define in this Excel Connection Manager.

Step 3: Retrieve data from a SQL table, and Fast Load into the Excel Summary sheet (or table) we just created.

Here is the OLE DB Source that gets data from a SQL table.

Here is the Excel Destination – the Summary sheet (or table).