Newbie to DTS - Email text contents

[sanjdhiman]

Hi there my question is two fold.

1. As you guys are all professionals in DTS packages, is there a book out there that is very good for beginners who want to become experts at DTS packages? Any recommended reading or online tutorials

2. I have an issue with a DTS package... I want to email the contents of a text file generated in the body of the email. At the moment the package uses the Email Task and the Textfile task. It sends the data on success of the textfile being populated from the database.

Database
connection
|
|
| -- SQL Query
|
Text File
{
{
{ -- On success
{
email Task


Thanks in advance

Sanj

 

[JayKusch]

First ... a book to check out!

I have found the book "Microsoft SQL Server 2000 DTS Step by Step by Carl Rabeler" to be very informative. The book guides you through the many connections, tasks, precedence constaints and other aspects of DTS. What is great is that the chapters and subject matter build on one another so that when you have finished the book, you have created a data loading application that loads tables for a data warehouse.

This is a very good "Beginners" Book. Once you have read this, grab the "Microsoft SQL Server 2000 Data Transformation Services DTS by Timothy Peterson" book as your reference guide.

http://www.amazon.com/exec/obidos/ASIN/0735619166/ref=sib_rdr_dp/104-8539628-2728723

http://www.amazon.com/exec/obidos/t...gy_img_2/104-8539628-2728723?v=glance&s=books

Thanks

J. Kusch

 

[JayKusch]

As for you DTS email issue, I am not following what the problem is?

Thanks

J. Kusch

 

[sanjdhiman]

Hi Jay

thanks for the info regarding the books I will take a look at them..



What im trying to do with the dts job is have the data from a sql query exported out to a text (flat file) file. This file is then to be sent not as an attachment but the contents of the text file to be placed into the email body. What i have at present is the text file being attached to the email.. which what i dont want.

any ideas?

sanj

 

[sandylou]

I am not an expert(more of beginner), but you are probably going to have to do it in an Active X Transformation and then look at the outlook object model in order to create the email. The SQL Query will need to be your "Parent" on completion to an Active X Transformation. This might get you started.

 

[JayKusch]

How large of a letter are we talking ... character-wise? What would be the max length this file could achieve?

Thanks

J. Kusch

 

[yksvaan]

If you have IIS running on sql server machine, you can easily send self-formatted SMTP mail messages within ActiveX task (search forum), so no Exchange configuration circus. Some settings might be needed on IIS to make it work, but normally it works out-of-the-box, of course some settings on DNS and/or firewall/networking may prevent this, but these should be issues to overcome.

BTW, what was mentioned in the first post, I don't anymore even dream of becoming an expert at anything

Cheers

[blue]Backup system is as good as the latest recovery[/blue]

 

[sanjdhiman]

the size of the file will only be a single line...

 

[JayKusch]

Would this work OR will the text file you are creating contain multiple feilds provided by the query?

--  Variable for BUILDING Email structure

DECLARE	@nRecipients		AS VarChar(1000)
DECLARE	@nCopy_Recipients	AS VarChar(1000)
DECLARE	@nSubject		AS VarChar(500)
DECLARE @nMessage		AS VarChar(8000)

DECLARE @QueryResults		AS VarChar(400)

SET	@nRecipients		= ''
SET	@nCopy_Recipients	= ''
SET	@nSubject		= ''
SET	@nMessage		= ''
SET	@QueryResults		= ''

--  GET your Text line and load it up

SELECT	@QueryResults = MyColumn
FROM	MyTable
                                                                                            '
--  Build Recipent List for Email Alert

SET	@nCopy_Recipients	= 'User1@MyCompany.com'
SET	@nRecipients		= 'User2@MyCompany.com'

--  Build SUBJECT for Email Alert
	
SET	@nSubject =	'This is your file ' + RTRIM(LTRIM(Convert(Char,DATEADD(dd,-1,GetDate()),101)))

--  Build MESSAGE for Email Alert

SET	@nMessage =	'Good Afternoon, ' + Char(10) + Char(13) + char(10) + char(13) + 
			'The email you are receiving contains the File ' +

			RTRIM(LTRIM(@QueryResults)) +  

			
			 Char(10) + Char(13) + Char(10) + Char(13)	+
			 'If there are any concerns, issues or question of the content or creation ' +
			 'of the file, please contact ME at (123) 456-7890.'  +
			 RTRIM(LTRIM(@nMessage)) + 
			 Char(10) + Char(13) + Char(10) + Char(13)	+
			 Char(10) + Char(13) + Char(10) + Char(13)	+
			'Thank You' + 
			 Char(10) + Char(13) + Char(10) + Char(13)	+
			'My Company'



--  SEND Out Email Alert

EXEC	MASTER..xp_sendmail
	@Recipients		= @nRecipients,
	@Message		= @nMessage,
	@Copy_Recipients	= @nCopy_Recipients,
	@Subject		= @nSubject

Thanks

J. Kusch