Syntax Error inserting an error into an error log

• andrew.curren

  Old Hand

  Points: 326

  More actions

  September 19, 2014 at 8:26 am

  #296574

  I have a SSIS_Log table used for custom error logging which works great however as part of my SSIS package I wanted to test an actual error by purposely breaking one of the stored procedures behind it.

  I have an OnError event handler which inserts errors using the following code:

  "INSERT INTO [dbo].[SSIS_Event_Log] ([RunNumber] ,[EventType] ,[PackageName] ,[TaskName] ,[ProcedureName] ,[EventCode] ,[EventDescription] ,[PackageDuration] ,[ContainerDuration] ,[InsertCount] ,[UpdateCount] ,[DeleteCount] ,[Host])

  VALUES

  (

  " + (DT_STR, 8, 1252) @[User::RunNumber] + "

  ,'OnError'

  ,'"+@[System::PackageName] +"'

  ,'"+@[System::SourceName] + "'

  ,'"+@[User::PackageTaskName] + "'

  ," + (DT_STR, 15, 1252) @[System::ErrorCode] + "

  ,'" + @[System::ErrorDescription] + "'

  ," + (DT_STR, 8, 1252) DATEDIFF( "ss", @[System::StartTime] , GETDATE() ) + "

  ," + (DT_STR, 8, 1252) DATEDIFF( "ss", @[System::ContainerStartTime] , GETDATE() ) + "

  ," + (DT_STR, 8, 1252) @[User::InsertCount] + "

  ," + (DT_STR, 8, 1252) @[User::UpdateCount] + "

  ," + (DT_STR, 8, 1252) @[User::DeleteCount] + "

  ,'" + @[System::MachineName] + "'

  )"

  I have renamed a column in my store procedure from FirstName to FirstName1 simply so that the procedure fails as the table contains a column named FirstName. So as expected I get the error which I am hoping for

  [Execute SQL Task] Error: Executing the query "exec [dbo].[usp_SSIS_Log_Testing]

  ..." failed with the following error: "Invalid column name 'FirstName1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  So this is the error I wanted so that I can log this and control whether I want the package to fail or restart etc. what I want is for this to then simply be written to the SSIS_Log table that I have hence the script higher.

  Bizarrely the error is not recorded in the table instead the Execution Results show the following error:

  [Execute SQL Task] Error: Executing the query "INSERT INTO [dbo].[SSIS_Event_Log] ([RunNumber] ,..." failed with the following error: "Incorrect syntax near 'FirstName1'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  What I don't understand is why instead of the error just being recorded into the log table am I getting a Syntax Error, I have been over all the column data types etc. and there are no problems, the OnError does log other errors ok but not in this case. Could this be a red herring in that there is validation on the procedure and the invalid column name?