SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using DTS to Generate and Email Excel Reports

By Joseph Sack, (first published: 2004/05/03)

This article explains how to create a DTS Package that performs the following tasks:

  1. Deletes existing rows from the Excel Spreadsheet
  2. Populates the spreadsheet with SQL Server data
  3. Emails the attached Excel spreadsheet using ActiveX and CDO
In my step-by-step example, I will be using the pubs database to export the contents of the 'authors' table.

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

When configuring the Excel spreadsheet connection, I prefer a URL for storing my reporting files, rather than a local path. Remember that DTS, when executed, will always run on the server/client where it is executed from. This means that if you build and test the package from your client workstation using, for example, the path "D:\temp\whatever.xls", that same directory had better be on the server where you schedule the package. If it is not, the package will fail. Using a URL will side-step this issue, assuming the account running your package has permissions to the URL in question. Most of you will be testing my example on your home computer, however, so in this example, I will be placing the Authors.xls spreadsheet under my c:\Temp directory.

Add a transform data task between the Pubs (SQL Server source) and the Excel (destination) connections. Configure the transformation by double clicking the black arrow.

On the Source tab, select the 'authors' table.

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 okay, you will now see the destination fields that will appear in the spreadsheet.

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

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

Within this task, select 'Excel' as the "Existing Connection". Be sure to change this, so that you don't erroneously delete the authors table on the SQL Server instance (by the way - it is better to export to Excel from a view, to avoid modifying SQL Server objects).

Within the SQL statement, enter in DROP TABLE 'authors' and GO. There is a limitation in the Excel driver that effects the 'DELETE' keyword. If you attempt to use it, you will receive the error message, "Deleting data in a linked table is not supported by this ISAM". Instead, we remove the Excel table entirely with the DROP TABLE 'authors' command.

After the DROP statement, type in GO, then paste the CREATE TABLE text that you copied earlier. This recreates the Excel dataset.

Select OK when finished and create an 'ON SUCCESS' workflow, with the new Execute SQL Task as the first step.

In your DTS Package, Create an ActiveX script that generates the email. Note: this assumes you are running the DTS package from a client or SQL Server machine that is running the SMTP service (and that your network infrastructure allows for it). Here is the text for the ActiveX script that uses CDO to send the spreadsheet. Change the email's TO, FROM, Subject, and TextBody as you wish:

Function Main()
Dim iMsg 

set iMsg = CreateObject("CDO.Message")

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

objMail.From = " youremail@email.com"
objMail.To = " youremail@email.com"
objMail.AddAttachment ( "c:\temp\authors.xls")
objMail.Subject="Authors Spreadsheet"
objMail.TextBody = "Spreadsheet"
Set objMail = nothing

Main = DTSTaskExecResult_Success
End Function

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

Save the package, and schedule it to execute via a SQL Server Agent Job.

In conclusion, I use this method of reporting as a effective and simple means of providing data to my co-workers and clients. Providing data in an Excel spreadsheet allows the recipient to massage the data to their liking. Emailing the spreadsheet is also a convenient option for those recipients who do not have access to an FTP directory or File Share. As for the execution of the DTS package; you can schedule your DTS package to run at specified dates and times, or you can simply run the package ad hoc.

I hope you find these techniques a useful addition to your DTS toolbox.

Total article views: 39742 | Views in the last 30 days: 34
Related Articles

SSIS Package to create Excel Spreadsheet fails in SQL Agent

SSIS script task to create Excel spreadsheet fails when run in 2005 SQL Agent


Create/write to an Excel 2007/2010 spreadsheet from an SSIS package

Excel spreadsheets are useful for distributing data generated by SQL Server. EPPlus is an open sourc...


data load from excel spreadsheets automatically

data load from excel spreadsheets automatically


Import Excel Spreadsheet

How Do I Import Excel Spreadsheet as a table in SQL 2005


Generate Excel Spreadsheets

Have RS generate Excel Spreadsheets

sql server 7