Post reply

SSIS Package is needed

  • October 5, 2012 at 1:40 am

    #265404

    hi,

    i am planing to automate one query which will run at particler time and the results will be emailed to my mail.

    but i have got problem in how to send the results tab to email.

    can any one help on this?

    Thanks

    Arjun

  • October 5, 2012 at 3:03 am

    #1545946

    You need to export the results to a file (CSV, Excel) in SSIS then add that file as an attachment in the email task.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • October 5, 2012 at 3:28 am

    #1545960

    no jonson..

    Object variable and i would like to send this results using a Send Mail Task using the following expression on the MessageSource.

    "Please find attached data summary" + SUBSTRING( @[User::myVariable] ,1,3990)

    + ""

  • October 5, 2012 at 3:30 am

    #1545964

    Probably, you have found an answer to your question by now. This answer is for others who might stumble upon this question. I don't think you can use object variable in expression. You need to loop through the query result object and format it to a string so that you can send the query output in an e-mail message. You can also export the data to a file and send the file as an attachment. That is another possible option. This example shows how to loop through the query result set to form the message body that will then be emailed using Send Email task.

    Step-by-step process:

    Create a table named dbo.EmailData using the script provided under SQL Scripts section.

    Screenshot #1 shows sample data that Execute SQL task will query and send it in an e-mail in this example.

    On the SSIS package, create 5 variables as shown in screenshot #2.

    On the SSIS package, place the following tasks: Execute SQL task, Foreach loop container, Script task within the Foreach loop container and Send Email task.

    Configure the Execute SQL task as shown in screenshots #3 and #4.

    Configure the Foreach loop container as shown in screenshots #5 and #6. Variable mappings section shows the order in which the query result columns appear and how they are assigned to SSIS variables. These variables will be used to form the email message inside the Script task.

    In the Script task, replace the code with the one shown under the Script task code section. The script task has very simple plain text email message formatting.

    Configure the Send Email task as shown in screenshot #7. You need to configure it with valid email address in From and To fields.

    After configuring the Control flow tasks, your package should look like as shown in screenshot #8.

    Sample package execution is shown in screenshot #9.

    E-mail sent by the package is shown in screenshot #10. Some information have been removed from the screenshot. You can compare the table data shown in screenshot #1 with this email output and they should same.

    Hope that helps.

    SQL Scripts: .

    CREATE TABLE [dbo].[EmailData](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [ItemId] [varchar](255) NOT NULL,

    [ItemName] [varchar](255) NOT NULL,

    [ItemType] [varchar](255) NOT NULL,

    [IsProcessed] [bit] NULL,

    CONSTRAINT [PK_EmailData] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]

    GO

    Script task code:

    C# code that can be used only in SSIS 2008 and above. .

    /*Microsoft SQL Server Integration Services Script Task

    Write scripts using Microsoft Visual C# 2008.

    The ScriptMain is the entry point class of the script.

    */

    using System;

    using System.Data;

    using Microsoft.SqlServer.Dts.Runtime;

    using System.Windows.Forms;

    namespace ST_7f59d09774914001b60a99a90809d5c5.csproj

    {

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

    #region VSTA generated code

    enum ScriptResults

    {

    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    };

    #endregion

    public void Main()

    {

    Variables varCollection = null;

    string header = string.Empty;

    string message = string.Empty;

    Dts.VariableDispenser.LockForWrite("User::EmailMessage");

    Dts.VariableDispenser.LockForWrite("User::ItemId");

    Dts.VariableDispenser.LockForWrite("User::ItemName");

    Dts.VariableDispenser.LockForWrite("User::ItemType");

    Dts.VariableDispenser.GetVariables(ref varCollection);

    //Set the header message for the query result

    if (varCollection["User::EmailMessage"].Value == string.Empty)

    {

    header = "Execute SQL task output sent using Send Email Task in SSIS:";

    header += string.Format("{0}\t{1}\t\t\t{2}", "Item number", "Item name", "Item type");

    varCollection["User::EmailMessage"].Value = header;

    }

    //Format the query result with tab delimiters

    message = string.Format("{0}\t{1}\t{2}",

    varCollection["User::ItemId"].Value,

    varCollection["User::ItemName"].Value,

    varCollection["User::ItemType"].Value);

    varCollection["User::EmailMessage"].Value = varCollection["User::EmailMessage"].Value + message;

    Dts.TaskResult = (int)ScriptResults.Success;

    }

    }

    }

    Screenshot #1:

    Screenshot #2:

    Screenshot #3:

    Screenshot #4:

    Screenshot #5:

    Screenshot #6:

    Screenshot #7:

    Screenshot #8:

    Screenshot #9:

    Screenshot #10:

  • October 5, 2012 at 8:26 am

    #1546121

    Good post Div 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply