This article explain how generate excel file using DTS Package in SQl server and Send mail in given email address.
SQl server 2000, Later
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
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
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
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.
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.