Home > SSIS Best Practices > SSIS – Create Excel Data File (1)

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


Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: