Export data from SQl server to excel file and send mail with attachment

Introduction

This article explain how generate excel file using DTS Package in SQl server and Send mail in given email address.

Technologies

SQl server

Prerequisite

SQl server 2000, Later

Implementation

Within a new DTS Package, in DTS Designer, create two Connection objects; the first for your SQL Server UserRoll database, the second for your Excel spreadsheet.
 

On the Destination tab, you will be prompted to create the Excel table. Before pressing the OK button, highlight the SQL text, and copy the generated CREATE TABLE code (using CTRL-C) to a notepad (for later use). Once copied, press OK. After pressing ok, you will now see the destination fields that will appear in the spreadsheet.

On the Transformations Tab, Select OK, accepting the row transformation defaults. See below figure 

Next, in your DTS package, create a new Execute SQL Task.

For that right click on click Execute SQL Task or in Task Manu click on Execute SQl Task

In SQl Task Enter in DROP TABLE ‘Users’ and Go , then paste the CREATE TABLE text that you copied earlier. This recreates the Excel dataset.

Right click on Transform data task then select workflow properties like

Following window open Click on new button its look and select delete user workflow like

Now package looking like below figure

Send mail to admin to given email address Take one ActiveX script task to generate the email.

Past vb script on that ActiveX script

Function Main()

Dim objMail   
Set objMail = CreateObject("CDO.Message")

objMail.From = " Fromemail[at]email.com"objMail.To = " Tomail[at]email.com"objMail.AddAttachment ( "C:\data\users.xls")
objMail.Subject="User Excelsheet    "objMail.TextBody = "USers Excel Sheet    "objMail.Send
Set objMail = nothingMain = DTSTaskExecResult_Success
End Function

Fromemail@email.com is which u wants to send mail from given email address.
Tomail@email.com is which u wants to send mail with attachment Exported excel file

I
objMail.TextBody will send plain text massage like

objMail.TextBody = "USers Excel Sheet"

if you want to send mail with html text then change it to

objMail.HTMLBody = "USers Excel Sheet"

Past above script to activate script task like

Lastly, create an On Success Workflow between the Excel connection Transformation task and the ActiveX Email task.

Conclusion

Using this we can easily export data from SQl server to excel file and send excel file with attachment to admin on given e-mail address. Enjoy!. Happy Coding.