DTS package with global variable and send mail task

Posted on 2006-05-17
Medium Priority
Last Modified: 2008-02-01
Hi, all

I am new on creating DTS package.

I have set up connetion, one Execute SQL task which outputs some item descriptions. Now I want to receive an email containing those item description. How would I do? I have set up a global variable for the output, but on the email how can it show up?

appreciate your help

Question by:Elaine_Shi
LVL 30

Assisted Solution

nmcdermaid earned 400 total points
ID: 16704582
Do you actually want those description to go into a database?

If so then I would suggest

1. Importing them into a table
2. Using xp_smtpmail (http://www.sqldev.net/xp/xpsmtp.htm) to extract them from the table and email them

If you don't actually want this data in a database then I would suggest using some form of automation such as VBScript or VBA to send an email using the outlook object model (assuming you have that installed)


Accepted Solution

rommeljohnsantos earned 600 total points
ID: 16707952
You may try this:

     First, the Send Mail Task is used to send the email.  Fields like To, CC, or Subject can already be populated.  After the global variable is assigned of its value, the Dynamic Properties Task may be use to assign the message text box of the Send Mail Task with the value of the global variable.
     The package flow may look like this:

                              Execute SQL  ------------>   Dynamic Properties   ------------->   Send Mail
                                   Task                                     Task                                        Task

      The Execute SQL Task may no longer be used to retrieve the result which would be assigned to the global variable, in case this is the initial design, as Dynamic Properties Task can be used instead.
      For specifc instructions for assigning global variable value to the message text of the Email:

            1. Double-click the Dynamic Properties Task.
            2. Click Add Button.
            3. Expand Tasks node.
            4. Choose the Send Mail Task.
            5. On the right side of the window, double-click the Message Text property. (The Add/Edit Assignment appears)
            6. On the source drop-down list, choose Global Variable.
            7. Choose the required variable name for the variable drop-down list.
            8. If the Global Variable already has a value, this would appear in the Preview field.
            9. Click ok --> Close ---> OK.

      For assigning value to the Global Variable from a result of a query, you may follow the steps above, but on the 6th step, choose Query.

I hope this helps.

Author Comment

ID: 16808820
Rommel's suggestion is quite clean and easy to understand, but I just have few problem.  I like your idea.

nmcdermaid's idea is also feasible but I think input into a txt file will be much easier.

Thank you all for your help. I will split the points.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16812702
>>Rommel's suggestion is quite clean and easy to understand, but I just have few problem. <<
I know what you mean.  The problem is that you need to install and configure SQL Mail, as well as install an Outlook client and setup a profile on the server.  Most shops won't even consider that.

Go with nmcdermaid solution using XPSMTP it is afar simpler solution.

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question