Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to pass system variables to event hander ?

Answered How to pass system variables to event hander ?

  • Wednesday, February 06, 2013 12:34 AM
     
     
    Sign In to Vote
    0

    hi there:

      On Event handers in SSIS , I've creaed a Execute SQL task to send out email alert when the event is OnError.

    In the alert, I'd like to pass the package name  ( System::PackageName) to the msdb..sp_send_dbmail    .

    On Execute SQL task, I've added a varaible System::PackageName as Input Direction and datatype is NVARCHAR, parameter name is 0.

    Now, how to pass it to the SQLStatement?

    Thanks

    Hui

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     

All Replies

  • Wednesday, February 06, 2013 1:31 AM
     
     
    Sign In to Vote
    0

    Use the question mark (?) as a place holder in your SQL Statement for the parameter.  The first question mark maps to parameter named 0.  The next question mark is parameter 1, etc.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Wednesday, February 06, 2013 1:38 AM
     
     
    Sign In to Vote
    0

    hi Russ, I've tried but failed, where should I put ?, inside the statement directly?  I guess if sql statemetn is no good, I may switch to variable.

     Error msg:

    [Execute SQL Task] Error: Executing the query "

    -- set up the mail job:
    DECLARE @tableHTML VARCH..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    -- set up the mail job:
    DECLARE @tableHTML VARCHAR(MAX)

    DECLARE @email_recipients NVARCHAR(256)
    DECLARE @email_subject      NVARCHAR(256)

    SET @email_recipients = 'xxxxxxxxxxxxx'
    SET @email_subject =  '### Package  ' +? +'Failed on Server '+@@SERVERNAME+' ####'

    SET @tableHTML = 'Package failed on '+@@SERVERNAME   +' Please check!'


    EXEC msdb..sp_send_dbmail       
     @profile_name = 'SSIS_MAILER_PROFILE'       
    ,@recipients = @email_recipients  
    ,@subject = @email_subject
    ,@body = @tableHTML,
    @body_format = 'TEXT'      

       
    GO

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, February 06, 2013 1:51 AM
     
     
    Sign In to Vote
    0

    Are there any other errors associated with this?  Frequently there are more than one error, one of which has more information.

    Also, try running this query on the server using SSMS.  Replace the ? with any string (' Package Name').  Do you get an error then?

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Wednesday, February 06, 2013 1:52 AM
     
     
    Sign In to Vote
    0

    Are you using an OLE DB Connection manager for your Execute SQL task?

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Wednesday, February 06, 2013 3:36 AM
     
     
    Sign In to Vote
    0
    Cant you use Send mail task on error?

    Thanks, hsbal

     
  • Wednesday, February 06, 2013 5:34 PM
     
     
    Sign In to Vote
    0
    hi Russ, yes I am using OLE DB connection manager and I've copied script to ssms and replace the question mark with a string, it went through without an issue.

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, February 06, 2013 5:36 PM
     
     
    Sign In to Vote
    0

    harry, send mail task is an alternative way but i am using execute SQL Task right now.

    thanks

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, February 06, 2013 5:45 PM
     
     
    Sign In to Vote
    0
    In SSIS were there multiple errors?  Normally Execute SQL task returns more than one error for each error.  The first is basically "there is an error."  The second is the actual error.  What you sent is the "there is an error" message.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Wednesday, February 06, 2013 5:53 PM
     
     
    Sign In to Vote
    0

    hi Russ, for the same SQL statement in Exec SQL task, when I added the question mark, it failed. However, when I removed it and kept the rest the same, the task worked.

      The question mark was placed in the SET statement and maybe it can not be concatenated?

    SET @email_subject =  '### Package  ' +? +'Failed on Server '+@@SERVERNAME+' ####'

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, February 06, 2013 7:54 PM
     
     Answered
    Sign In to Vote
    0
    Hi russ, I think I've figured it out, I need to put into the expression of sqlStatementSource of Send email task. In this way, I can directly reference system variables and get rid of parameter mapping part.

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --