There is a common need where we are required to generate a report from a database and send that report to group of people. In this article we will see as how we can use SSIS to design a work flow to first create an excel report and then send in an Email.
We will be using BIDS as our IDE to create the package.
- Open BIDS and Create a New Project. We will name our project ExcelAutomation
- We will be using File Task System, Data Flow Task and Send Mail Task to design our work flow. At first, there will be a little Red Cross sign as we have to do the configuration.
File System Task: We are using this to overwrite the existing excel file with a blank file with same header before Data Flow task populate the data from SQL Server to Excel file. This is done
Data Flow Task: This will transfer the data from Database (source) to Excel Destination.
Send Mail Task: This will send email to recipients with attachment.
We will name our excel destination as "Vacation report". We will configure it to delete any existing file with same name. Right Click on File Task-> Click Edit
This will bring up a window where we can choose "Delete file" from operation. Also make a new source connection connection as shown below:
Create a new the Destination Source by clicking on new source as shown below, from Usage Type choose Existing file. Click on Browse and select your file. In the same way we can configure Source Connection as well.
- Once source and destination are configured we will configure Data Flow Task as below having ADO NET as source and Excel Destination.
Configure ADO Net as below:
Create a ADO NET Connection by clicking on New Connection and select.
The example in this article is from adventure works and we have selected SQL command from Data access mode as below:
In the same way we will create Excel Connection Manager. Right click Excel Destination and create New Connection Manager.
Now we configure Send Mail task in the same way by right click on task and click Edit which will bring up below window. Click on New Connection and SMTP server name needs to be listed. Make sure you have those before starting this task.
Specify the details like
- From: Name of mail box for outgoing emails
- To: Name of recipients receiving the email
- Subject: In our Case we are giving Vacation Report
- Message Source: Email Body that you want to send
- Priority: High, Normal or medium.
- Attachment: Path of the excel file (in our case vacationreport.xls)
- Last, but not least, we want to give specify precedence constraint in case any task fails. In our case we are using Notify Operator Task, but other send Mail task can also be used in order to notify Administrator if no Operator is included in database server.
Once this is all complete we can execute our package and if the entire task turn to green than package is successful.