Debugging SQL Server Integration Services Scripts

Debugging SSIS scripts is not the easiest of all things. Below we propose a number of ways to debug which we've seen in a presentation by Alan Mitchell - SQL Server MVP.

1. MsgBox messages - good during development, but not any good for production environments since once the MsgBox pops up it WILL wait for user input before the task proceeds.
2. Fire Events - the Progress / Output windows can also be used to ouput your debugging information.

Imports Microsoft.SQLServer.DTS.RunTime

DTS.Events.FireInformation
DTS.Events.FireBreakPointHit
DTS.Events.FireCustomEvent
DTS.Events.FireError
DTS.Events.FireProgress
DTS.Events.FireQueryCancel
DTS.Events.FireWarning

OR

Me.ComponentMetaData.Fire<...>

3. Using Windows tracing events - System.Diagnostics.Trace.WriteLine, and then use a program such as Sysinternals DebugView to watch for this information.