locked
send email through a stored procedure using values from table RRS feed

  • Question

  • Hello, Not sure where my question should be in sql or SSIS forum.

    i have a table like..

    source      type        importfilepath                     exportfilepath

    BOM          1             c:\import\BOM\BOM.xls         c:\export\BOM\BOM.xls

    RTM           1             c:\import\RTM\RTM.xls          c:\export\RTM\RTM.xls 

    GSM           2            c:\import\GSM\GSM.xls          c:\exmport\GSM\GSM.xls

    i want to use a stored procedure which would send an email attaching the file from the location in table column exportfilepath.

    I have an SSIS package with the 3 variables iBom, iRtm, iGsm and these variables would have values either 1 or 0, 1 or 0 and 2 or 0 respectively.

    so depending on those values i want to send file when BOM, RTM and GSM have 1,1 and 2 resp. from table above.

    can somebody tell me how do i write a script for that which would decide which source is valid and then pass the values from table and send email including the file.

    I already have the stored procedure to send email with file attachment for 3 sources but can i do that just in one script?

    Tuesday, May 15, 2012 9:21 PM

Answers

  • To send the file use the SendMail Task SSIS canned component. There http://dwhanalytics.wordpress.com/2010/05/07/ssis-attach-multiples-files-in-send-mail-task/ you see that property that you need to set off the shredded recordset in the ForEach loop

    Arthur My Blog

    • Proposed as answer by Eileen Zhao Friday, May 18, 2012 5:03 AM
    • Marked as answer by Eileen Zhao Tuesday, May 22, 2012 5:11 AM
    Wednesday, May 16, 2012 7:10 PM

All replies

  • I would approach this like follows:

    Create an Execute SQL Task to get the data from the table and then pass its result as full resultset to a package variable.

    Then shred it in a ForLooop processing each row and passing the needed values to a parametrized SendMail Task (for attachment).

    See how you can shred the recordset: http://www.sqlis.com/post/Shredding-a-Recordset.aspx

    This way you do not need to script too much.


    Arthur My Blog

    • Proposed as answer by RamJaddu Wednesday, May 16, 2012 1:36 PM
    Wednesday, May 16, 2012 1:31 PM
  • Hello ArthurZ, i am exactly using the same logic from the link you gave in your answer.

    I have foreach loop container, storing data from the table in variable dsConfig in execute sql task and have that variable in foreach loop container as a ADO object source variable, mapping source, type, importfilepath and exportfilepath in variablemaaping.

    In foreach i have execute sql having script,

    declare @source varchar(10)
    select @source = source from softdollardata
    
    If @source = 'BOM'
    select exportfilepath from softdollardata
    where source = 'BOM'
    
    else if @source = 'RTM'
    		select exportfilepath from softdollardata
    		where source = 'RTM'
    		
    else if @source = 'GSM'
    select exportfilepath from softdollardata
    where source = 'GSM'
    
    else RAISERROR ('There is no error/or no data', 16,1)
    and have a variable exportfilepath in result set. I have one more execute sql task in Foreach to send email and has code.
    Exec [PWOSendDBMail]
    @TableName   ='',
    @Body    ='dataload done with errors. Attached is the error report',  
    @BodyFormat   ='HTML' ,
    @CallerType   = 'Prc',-- Job Level or Procedure Level[JOB/ PRC]
    @CallerName   ='SoftDollarProcess', -- Name of the procedure or Job 
    @FileAttachments = ?
    @ParentPackageName = '',
    @Success   = 'Y'
    I have @FileAttachments = ? coz want to send file so trying to get the value of exportfilepath from parameter mapping

    I am getting error for variable dsconfig as Variable "User::dsConfig" does not contain a valid data object


    • Edited by JoJo_1 Wednesday, May 16, 2012 6:41 PM
    Wednesday, May 16, 2012 6:39 PM
  • To send the file use the SendMail Task SSIS canned component. There http://dwhanalytics.wordpress.com/2010/05/07/ssis-attach-multiples-files-in-send-mail-task/ you see that property that you need to set off the shredded recordset in the ForEach loop

    Arthur My Blog

    • Proposed as answer by Eileen Zhao Friday, May 18, 2012 5:03 AM
    • Marked as answer by Eileen Zhao Tuesday, May 22, 2012 5:11 AM
    Wednesday, May 16, 2012 7:10 PM