DTS with email output

[StevenK]

I'm new to DTS - but have been able to generate a DTS package to output a file (text) using a complex query.

Ideally now I'm wanting this file to be generated once a day and e-mailed to a target audience.

What do I need set up to enable myself to do this ?

Any helpful starting points would be gratefully received.
Thanks in advance.
Steve

 

[123FakeSt]

That's going to depend on your email service you are using.

Exchange? Lotus? Groupwise?

If you have an internal SMTP service running on a server, do a search for 'Send CDO Message VBScript'

The early bird gets the worm, but the second mouse gets the cheese.

 

[StevenK]

We've tried setting up 'SQL Mail' but to no avail.
We've installed Outlook on the server (where SQL Server is installed and running) - and set up an 'administrator' account - as this is the account under which the SQL services are running.
When we run the 'Test' from the setup of 'SQL Mail' this fails.
Can anyone give me an idiot proof guide as to how to set up 'SQL Mail' and then make use of this in a DTS Package??

Any further help would be appreciated.
Thanks in advance,
Steve

 

[123FakeSt]

This one looks pretty good:

http://www.databasejournal.com/features/mssql/article.php/3345091

The early bird gets the worm, but the second mouse gets the cheese.

 

[aftertaf]

i have an alternative:
xp_smtpmail

http://www.opcode.co.uk/components/xpsmtp.asp

works a treat

Aftertaf
We shall prevail, and they shall not

 

[mutley1]

Steve,

when you say 'we set up an administrator account', I take it you mean a domain account and not local admin. SQL mail (if you are using Outlook) needs a domain account and the services should be running under that. E.g. if the SQL services are running under MYDOMAIN\SQLGOD then you'll need to set up a mailbox / profile that SQLGOD can access and put that profile in the SQL Mail section. If you mean administrator as in local administrator then it won't be able to use Outlook.

Even if you use xp_sendmail then some sort of profile will need to be set up as it will try to use the context of the owner of the job.

 

[mutley1]

Sorry - forgot to include the link....D'oh!

MS always a good place to look first....

http://support.microsoft.com/kb/263556

Cheers,

M.

 

[StevenK]

OK - thanks for the help so far.
We've now gotten SQL Mail set up from the SQL Server - which tests from here successfully (using the 'Test' button).

Can anyone now suggest a simple example of how I can test this process out to send an e-mail (with an attachment of an Excel spreadsheet generated from a database query ideally).

Is there a good source for such examples or can anyone talk me though an idiot proof one ?

Thanks again and in advance.
Steve

 

[mutley1]

Firstly, if you're new to DTS then this site is very good if you're going to get into it in more depth.

http://www.sqldts.com

If you have the basics already then to test it this is how I do mine.

1. Log onto the server that hosts SQL as the domain account running the services (and has the mail profile you set up for it) Don't do it from your machine.
2. Create a new DTS package and click on 'Task' then select Send Mail task.
3. When it pops up, give it a name like "send mail test". Select the mail profile from the drop down list, enter the password (remember this if you change passwords regularly!).
4. Enter the To details, subject etc. At the bottom there is a field called attachments. Click on add and browse to the file you want to send (guessing it is on the server) and add it.
5. Save the package and exit, then right click on it in the list of DTS packages and select "schedule". Create a schedule (say in 5 minutes time) and then just check the SQL jobs to see if it is created and then in a few minutes it will automatically run the package and hopefully you will receive your email.

It's important to remember when using the send mail task instead of xp_sendmail that if you edit the packages then it is safest to do so by beging phyically logged onto the SQL server as the domain account that you have set the outlook profile for. It gets a bit funny and if you do it from your machine using enterprise Manager then it tends to pick up your profile. this is only really if you change the profile name. For example, my profile on my machine is the deafult "Microsoft Outlook", but someone created one on the server called "MS Settings". If i change the package on my machine having opened up the send mail task it picks up Microsoft Outlook and it goes a bit pear shaped. Easy to fix (log onto the server as the donaim account, open the package and reset the mail. It should pick it up again automatically but you have to open it up then resave the package.

Hope this helps.

M.

 

[StevenK]

When I try step 2 from above (' Create a new DTS package and click on 'Task' then select Send Mail task') I get the error:
"Error Source : Microsoft Data Transformation Services (DTS) Package

Error Description: MAPI returned an error."

However I'm able to create and save the DTS package.
And have checked the message goes to the intended recipient.

Next question:
How would I set this DTS package up to run a query against a database, output the results to an Excel spreadsheet (or text file) and then attach this to the outgoing email?

Thanks for the help so far.
Steve

 

[mutley1]

OK,

A couple of question.

1. What's the frequency you want to send the spreadsheet - i.e. hourly, daily, weekly?
2. Do you want to append data to the spreadsheet or start from a blank template when the package runs?

Regarding the MAPI error, are you logged onto the server as the domain account that is running the services and has the mail account attached to it - i don'r mean through EM from your machine, i mean actually physically on the server? you will need to be when creating the DTS package to make it easier when selecting output files etc.

 

[StevenK]

Thanks again for your time...

1. What's the frequency you want to send the spreadsheet - i.e. hourly, daily, weekly?
ANSWER : usually we're looking at a weekly report - but some instances we may want reports that are run on a daily basis.

2. Do you want to append data to the spreadsheet or start from a blank template when the package runs?
ANSWER : We want to start a new spreadsheet in each instance.

3. The DTS package as created and saved is actually operating and will attach a text file (static location on the same server) when sending out emails as required.
However when we run the real DTS packages the location of the spreadsheet will not then be known - as it will not then be created (until run).

Thanks again.
Steve

 

[mutley1]

What I usually do is have a static template file (e.g. report_template.xls) in a fixed location and as part of the package I will copy it to the report name desired (using an xp_cmdshell SQL task in the package) then populate that in lter stages of the DTS. That means you have a clear start each time. does that sound feasible. If so I'll post some details as best i can to get you going (but will be tomorrow now).

 

[StevenK]

Yes - we could make use of an initial template file - which is populated, resaved (to a dynamic location - based on a datetime stamp perhaps?) and then e-mailed to the user(s) as required.

This would be a great help to me.

Thanks.
Steve

 

[123FakeSt]

I have a DTS package that loops through a table of reports, copies the .xls template from a 'template' area into a 'work' area. Then it reads the SQL and transformation scripts from the template area and defines the data pump task. After running the data pump, it affixes a timestamp and then saves it to an area accessible by the end user (much more preferrable than emailing). When there are no records left in the table marked 'hasrun' it resets them all to 'hasn't run' and exits the package until the next scheduled run.

I have found this to be extemely effective manner of report delivery (especially incorporating the OnOpen method of the Excel workbook), but it took me at least 8 hours to build and test the package. When I get a new request, i simply create the XLS file, SQL file, and transformation script then add it to the table and voila.

This might be a bit extreme for what you are trying to do though.

The early bird gets the worm, but the second mouse gets the cheese.

 

[StevenK]

Certainly an idea like this would work wonders for us in the long term.
At the moment we're only looking at a handful of basic reports (to be output to Excel - and either saved in an accessible area on the network or e-mailed to the users) - but in time no doubt this will grow in scale ...

Is there a basic example someone can provide that achieves the following:

- Runs a query and outputs to an Excel spreadsheet (perhaps initially using a template spreadsheet file from a known location on the server).
- Save the resultant Excel spreadsheet with a new filename (perhaps incorporating the date/time stamp to easily identify when this was generated).
- Possibly email this spreadsheet to a user.

This is what we're looking at achiveing as a first hurdle jump.
Any help with this would be appreciated.

Thanks again.

Steve

 

[mutley1]

Steve,

I started writing a document that would just do a simple select, put it in a copied template and change the name of the file etc. but it is going to take ages, and there are a plethora of possibilities depending on exactly what you want to do. If you want we can go through a moderator to exchange email addresses then I can actually call you and talk you through it. It's a lot simpler! If that would help you, then where are you based....UK?

Cheers,

M

 

[123FakeSt]

Well you would start with a SQL Server Connection and an excel connection to your template file. Then create a 'Transform Data Task' between the two of them. Right clicking that task and choosing properties will display the 'source' tab. Change the connection from Table/View to SQL query. Paste your SQL here.

Next create an ActiveX script. I use Global Variables to hold anything that may potentially change or be scalable, such as paths and filenames, but you can just hard code these.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()
'	This script will determine the work path and output path from the global variables
'	If there is a User/Group, it adds that to the output path
'	xxxxxx 2006 05 15

Dim objFSO, strSource, strDestination, strUserGroup

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Create the source and destination paths for the file
strSource = DTSGlobalVariables("strWorkPath").Value & "\" & DTSGlobalVariables("strCurrentFile").Value & ".xls"
strDestination = DTSGlobalVariables("strOutputPath").Value
strUserGroup = DTSGlobalVariables("strUserGroup").Value
If strUserGroup <> "" then strDestination = strDestination & "\" & strUserGroup
strDestination = strDestination & "\" & DTSGlobalVariables("strCurrentFile").Value & DTSGlobalVariables("strTimeStamp").Value & ".xls"

'Copy work file to its destination
objFSO.CopyFile strSource, strDestination

Main = DTSTaskExecResult_Success

Set objFSO = Nothing

End Function

Use a Send Mail Task instead if that's what you want ... I try not to email because it adds an additional point of failure/complexity (not to mention stress on the mail system) and all of my users are on the WAN, but if dealing with remote users it may be necessary.

Add workflow (On Success here because you don't want to proceed if the transform data task fails) to start the file move when the transform data task completes.

Notes about the Excel WorkPath:
Once you get this far, if you need the Excel workpath to be dynamic, you will want to use a Dynamic Properties Task to set it. With one of these set the Excel DataSource to a global variable. In a preceding step use an ActiveX script to assign the path to this global variable (and hopefully test its existence). I pull my paths from a table with this script.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
'	This step looks for the first report that is not already run.  If it
'	finds one then it sets the package to loop and assigns all of its
'	fields in the tblScheduledReport to global variables.  If none found
'	It tells the package to proceed to the exit
'	xxxxx 2006 05 15

Dim oConn, rst, strServer, strConn, strSQL, strReport, DTS, stpLoop, stpEnd

'Define loop and end steps
Set DTS = DTSGlobalVariables.Parent
Set stpLoop = DTS.Steps("DTSStep_DTSActiveScriptTask_8")
Set stpEnd = DTS.Steps("DTSStep_DTSActiveScriptTask_6")

'Get next report if there is one
Set oConn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
strServer = DTSGlobalVariables("strServerName").Value
strConn = "Provider=SQLOLEDB;Data Source=" & strServer & ";Initial Catalog=zDBA;Integrated Security=SSPI;"
oConn.Open strConn

strSQL = "SELECT TOP 1 * FROM tblScheduledReport WHERE schHasRun = 0 AND schIsActive = 1"
rst.Open strSQL, oConn
If rst.EOF then	'If no more, then end looping
	stpLoop.DisableStep = True
	stpEnd.DisableStep = False
	stpEnd.ExecutionStatus = DTSStepExecStat_Waiting
Else		'If more then enter loop
	stpLoop.DisableStep = False
	stpEnd.DisableStep = True
	stpLoop.ExecutionStatus = DTSStepExecStat_Waiting
	rst.MoveFirst
	DTSGlobalVariables("intCurrentID").Value = rst.Fields("schID").value
	DTSGlobalVariables("strCurrentReport").Value = rst.Fields("schName").Value
	DTSGlobalVariables("strCurrentFile").Value = rst.Fields("schFileName").value
	DTSGlobalVariables("strCurrentSQL").Value = rst.Fields("schSQLFile").Value
	DTSGlobalVariables("strCurrentExcelWorkPath") = DTSGlobalVariables("strWorkPath").Value & "\" & rst.Fields("schFileName").Value & ".xls"
	DTSGlobalVariables("strUserGroup") = rst.Fields("schUserGroup").Value
	DTSGlobalVariables("strCurrentTDT").Value = rst.Fields("schTDTFile").Value
End If

Set rst = Nothing
Set oConn = Nothing
Set stpLoop = Nothing
Set stpEnd = Nothing
Set DTS = Nothing
Main = DTSTaskExecResult_Success

End Function

The early bird gets the worm, but the second mouse gets the cheese.