question

I have a requirement where we need to Capture and log Stored Procedure progress messages to a flat file For this I have used the Script Task with custom .net code in order to call the wrapper SP and enabled the logging to re-direct the messages generated from the Script task to a log file(Text File). Screen Shot from the Script task where messages are generated after successful execution of the wrapper SP: ![alt text][1] **This only works when the Script task containing a call to the wrapper SP executes successfully i.e. when all the SP’s defined in the wrapper SP gets executed successfully.** The Script task throws an exception when any of the SPs within the wrapper SP fails due to which it rollbacks the transaction (Rollback defined in the SP definition) as well as rollback the messages that were generated out of the Script Task. I have even used calling the Stored procedure Asynchronously by defining Asynchronous Processing=True in the connection string but no luck with this. **I need to log the messages from the Script Task in the text file even when the Script Task throws an exception or any of the SP’s fail in the transaction.** Any suggestions around how to handle the exception and to re-direct the messages to the text file would be really appreciated. Please note, I won’t be making any changes to the wrapper SPs or the SPs within or with the rollback option in the SP. I have referred the below link for the above requirement: http://blogs.msdn.com/b/mattm/archive/2007/09/13/capture-print-messages-from-a-stored-procedure.aspx?CommentPosted=true#commentmessage Public Sub Main() Dim conn As New SqlConnection("server=SQL2012;Integrated Security=SSPI;database=DWH;Asynchronous Processing=True") AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage) conn.Open() Dim cmd As New SqlCommand() cmd.Connection = conn cmd.CommandType = CommandType.StoredProcedure cmd.CommandTimeout = 30 cmd.CommandText = "WrapperTestSps_RG" cmd.ExecuteNonQuery() conn.Close() Dts.TaskResult = ScriptResults.Success End Sub Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs) Dim sqlEvent As System.Data.SqlClient.SqlError For Each sqlEvent In args.Errors Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False) Next End Sub **Exception thrown by the script task when any of the SP in the wrapper SP fails:** DTS Script task has encountered an exception in user code: Exception has been thrown by the target of an invocation at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() **This is the error message that got generated by the failed Stored procedure, i need to log this message as well in the text file as this is the important objective for this requirement.** 02 Feb 2016 05:58:06:293 - Begin transaction... 02 Feb 2016 05:58:06:293 - Loading Test Data 02 Feb 2016 05:58:06:293 - Extracting and Transforming Test Data (0 row(s) affected) (1 row(s) affected) Msg 50000, Level 14, State 1, Procedure uspErrorHandler, Line 64 Date/Time 02-Feb-2016 05:58:06:543, Error 2627, Level 14, State 1, Procedure uspLoadTest, Line 109, Message: Violation of PRIMARY KEY constraint 'PK__#B4823D8__7AD04F110F218DD7'. Cannot insert duplicate key in object 'dbo.@tblTest'. The duplicate key value is (* ). 02 Feb 2016 05:58:06:560 - Rollback transaction... (1 row(s) affected) [1]: /storage/temp/3187-script-task-messages.jpg