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

No.of Views8534
Bookmarked0 times
By  Kirti.M.Darji   On  15 Feb 2010 22:02:16
Tag : Sql Servers , How to
Export data from SQl server to excel file and send mail with attachment
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com



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


SQl server


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.

Image Loading
Image Loading

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.

Image Loading
Image Loading

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

Image Loading


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

Image Loading

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

Image Loading


Now package looking like below figure

Image Loading


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

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

Image Loading

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

Image Loading


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.


Sign Up to vote for this article
About Author
Occupation-Software Engineer
Company-Maven-Infosoft Pvt.Ltd
Member Type-Expert
Location-Not Provided
Joined date-03 Jun 2009
Home Page-
Blog Page-http://kirtimdarji.blogspot.com/
I am Kirti M. Darji Senior Software Developer Having 3.5 Years Experience In Microsoft .Net(c#) Technologies.
Other popularSectionarticles
By:RRaveenDate Of Posted:10/21/2010 2:34:30 AM
Hi G
Hi G, if you are have issues to get it work, post your question in codegain message, we have experts to answer to your all questions.and you will get resolution faster.
By:Kirti M DarhiDate Of Posted:10/21/2010 12:40:33 AM
recreat the Excel dataset is not working
Hi it is working example are you messing something please if possible dts package first step is run delete record from excel file. physical file is available on given path else it give error so
By:GDate Of Posted:10/7/2010 12:13:05 PM
recreat the Excel dataset is not working
your step- where you say --it recreates the Excel dataset. are not working. Its giving error that table does not exitst. & even after second run- it does not delete previous contents in the excel file.-- it goes on appending the contes every time.
Leave a Reply
Display Name:
(not display in page for the security purphase)
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top