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.

image

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

image

The SourceConnection is defined in this File Connection Manager.

image

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

image

The Connection is define in this Excel Connection Manager.

image

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

image

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

image

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

image

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: