Post reply

How to use variable in Send Mail Task?

  • December 20, 2011 at 9:07 am

    #265211

    Hi,

    I made a SSIS project which is running fine.

    It has 4 steps.

    1) File System Task.

    2) Execute SQL task.

    3) Data Flow Task

    4) Send mail task.

    I am running a sp in execute sql task.

    Its fetching 3 dates as :-

    Declare @dt1 datetime,

    @dt2 datetime,

    @dt3 datetime

    set @dt1 = dateadd(d,-7,GETDATE())

    set @dt2 = dateadd(d,-1,GETDATE())

    set @dt3 = dateadd(d,-8,GETDATE())

    Its using @dt1 and @dt2 to fetch data from other tables.

    I want this @dt1 and @dt2 in my send mail task...

    beacuse when it sends mail to other people, they should know which dates IS IT FOR...

    I wanted to put it in the message source type - variable

    and then message source - The data is between @dt1 and @dt2...

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 21, 2011 at 12:56 am

    #1424062

    Put an expression on that variable you mentioned and use those two date variables there. Make sure you convert those dates to string if you want to include them in your text.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 21, 2011 at 7:10 am

    #1424209

    I attached 2 files here

    1 is hte execute sql task parameter mapping and

    2 are the variables.

    Please see in the 2 , the value for dt1 and dt2 , I want it to take from execute task (execute sp)

    But i see that dt1 and dt2 values i have to assign from hte calender, which i dont want as they are not fixed...they should be variable..

    Where I am wrong?

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 21, 2011 at 7:16 am

    #1424215

    The parameter mapping is to put parameters into the SQL statement.

    What you need is the result set:

    http://msdn.microsoft.com/en-us/library/ms141689.aspx

    And one with pretty pictures:

    http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 21, 2011 at 11:31 am

    #1424386

    And one with pretty pictures:

    http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

    This really helped me..

    Thanks a lot koen

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

  • December 21, 2011 at 11:17 pm

    #1424584

    SKYBVI (12/21/2011)

    And one with pretty pictures:

    http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx

    This really helped me..

    Thanks a lot koen

    Regards,

    Skybvi

    No problem, glad to help.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 25, 2012 at 6:57 am

    #1438213

    @koen.

    Sorry to disturb you again.

    I want to ask you :-

    1) I did some changes in the package and now i am getting in the e-mail

    "The data is from @dt1 and @dt2"

    It is not showing the results of @dt1 and @ dt2...

    2) i had 2 sql statements in my procedure ( in execute sql task)

    select * from xyz1

    select SUM(Sales_Amount) as Last_Week_total_sales from #abcd

    the results of xyz1 are coming fine but the results of #abcd are not coming in the excel sheet

    Is that the temp table output can't be seen in excel sheet?

    Regards,

    Skybvi

    Regards
    Sushant Kumar
    MCTS,MCP

Viewing 7 posts - 1 through 6 (of 6 total)

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