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

No.of Views6910
Bookmarked0 times
Downloads 
Votes1
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

 

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.
 

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

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

Image Loading

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

Image Loading

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.

 

 
Sign Up to vote for this article
 
About Author
 
Kirti.M.Darji
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
Comments
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
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
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