DTS package with global variable and send mail task

Posted on 2006-05-17
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

    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 ( 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)

    LVL 1

    Accepted Solution

    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

    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
    >>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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now