none
Error When trying to run stored procedure on failure RRS feed

  • Question

  • I have a stored proc that I use to log start / end of jobs in SSIS. It works perfectly for the start and if the job finishes successfully but fails with an error when called from the "failure" redirect of the job. It takes a number of parameters which I have checked and are all as I would expect and are not too long for the parameter definition but I am consistently getting the following error:

    [Execute SQL Task] Error: Executing the query "exec spu_Audit
    'FINISH',
    'FAIL',
    ?,
    '2 - SET SYST..." 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.

    the full SQL in the execute SQL task is:

    exec spu_Audit
    'FINISH',
    'FAIL',
    ?,
    '2 - SET SYSTEM VARIABLES',
    2,
    ?,
    ?,
    ?,
    '',
    'FAILED TO SET SYSTEM VARIABLES',
    'PACKAGE'
    ?,

    'SQL'

    the variables being passed to it in this instance are:

    User::RunID: integer - 16

    User::SourceSystem: varchar(50) - 'EQ_SH'

    System::UserName: varchar(50) - 'myusername'

    System::ExecutionInstanceGUID: varchar(100) - 'gobbledegook'

    System::PackageName: varchar(100) - 'STG_LOAD_PARENT'

    If I manually take the parameter values and plug them into the SQL above in SSMS the stored proc runs fine and it seems to be an error in calling the stored proc rather than running it.

    ResultSet in main window is set to 'None' as it simply updates a table in the database (this is the same for the start/success versions as well) and the connection type is OLEDB (exactly the same connection as the start/ success versions)

    If anyone has any ideas it would be most appreciated as this is an integral part of the package and I have no idea right now why it is failing

    Additional Info - the exact same stored proc works perfectly when called from a script task - just not from execute SQL task on failure of prior task

     


    Rgds Geoff

    • Edited by GeoffBarraclough Wednesday, December 1, 2010 11:33 PM additional info
    Wednesday, December 1, 2010 11:23 PM

Answers

  • Hi - yes it is OLEDB

    Just spent 10 mins writing a bunch of stuff out to test and found the issue - a missing "," after 'PACKAGE'

    I copy/pasted from a template so all versions of teh call had this error - thanks for replying to make me look harder at my code!!


    Rgds Geoff
    Thursday, December 2, 2010 6:00 AM

All replies

  • Is this on a event handler of the Execute SQL Task? I am trending towards looking at the Parameter mapping for the cause of the error in this case.
    Rakesh
    Thursday, December 2, 2010 1:53 AM
  • This is simply an Execute SQL task which is on the failure redirect from another Execute SQL task (although it may be on the failure redirect from any kind of task in reality)

    So I have

    1: Execute SQL Task - Log Start of Job

    2: Execute SQL Task - Run Job

    3: Execute SQL Task - Log success

    4: Execute SQL Task - Log Failure

    4: Is executed on failure of 2:

    1:, 3: & 4: all call the same stored proc but with different parameters

    1: & 3: work perfectly

    4: works perfectly when run from SSMS with the parameter values as they would be at run time, simply replacing the ? with the actual values


    Rgds Geoff
    Thursday, December 2, 2010 2:09 AM
  • what is your connection type? is it OLEDB ?

    could you paste your stored procedure parameters signature here? ( just put first lines of your stored procedure here)


    http://www.rad.pasfu.com
    Thursday, December 2, 2010 5:24 AM
    Moderator
  • Hi - yes it is OLEDB

    Just spent 10 mins writing a bunch of stuff out to test and found the issue - a missing "," after 'PACKAGE'

    I copy/pasted from a template so all versions of teh call had this error - thanks for replying to make me look harder at my code!!


    Rgds Geoff
    Thursday, December 2, 2010 6:00 AM
  • glad to solve,

    regards,


    http://www.rad.pasfu.com
    Thursday, December 2, 2010 6:03 AM
    Moderator