First published on MSDN on Sep 13, 2007
I recently helped with a customer issue where they had a long running stored procedure which output status messages periodically using PRINT statements. They wanted to capture these statements and output them into the SSIS log. Unfortunately, the Execute SQL Task doesn't support this (it's something we're considering for the future), but it's fairly easy to do through a script task.
Our stored procedure:
1: CREATE PROCEDURE SPWithPrint 2: AS 3: BEGIN 4: print 'very important status information...' 5: END 6: GO
Our script:
1: Public Sub Main() 2: Dim conn As New SqlConnection("server=(local);Integrated Security=SSPI;database=Test") 3: 4: AddHandler conn.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage) 5: 6: conn.Open() 7: 8: Dim cmd As New SqlCommand() 9: cmd.Connection = conn 10: cmd.CommandType = CommandType.StoredProcedure 11: cmd.CommandText = "[SPWithPrint]" 12: 13: cmd.ExecuteNonQuery() 14: 15: conn.Close() 16: 17: Dts.TaskResult = Dts.Results.Success 18: 19: End Sub 20: 21: Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As System.Data.SqlClient.SqlInfoMessageEventArgs) 22: Dim sqlEvent As System.Data.SqlClient.SqlError 23: For Each sqlEvent In args.Errors 24: Dts.Events.FireInformation(sqlEvent.Number, sqlEvent.Procedure, sqlEvent.Message, "", 0, False) 25: Next 26: End Sub
The print statements return the messages as InfoMessage events, which we catch with our handler and turn into SSIS information events.
When we run the package, we can see the message from the stored procedure in our progress window...
We're looking into adding this functionality to the Execute SQL Task as well, but hopefully this is an acceptable alternative until then.
