By Code and language-based developers may be initially turned off by the overwhelmingly GUI-based nature of SSIS packages. I’ll admit, I was. Thankfully Microsoft didn’t forget about us code geeks.
All Hail the Script Task
The Script Task allows developers to utilize VB and C#, and more importantly opens up the .NET Framework, which allows you to do some really cool stuff with your SSIS packages.
So how do you debug and troubleshoot your code?
Inside Visual Studio
SSIS packages don’t really have a code-friendly console in Visual Studio. The Output and Error List windows only show information regarding the execution and flow of the package (not helpful for us). The closest thing to a helpful console is probably the Watch window.
You could create a package variable to assign messages to in your Script Task, add an OnVariableValueChanged breakpoint to the task, and monitor changes in the variable’s value in the Watch window.
Or you could circumvent all of that and simply call the MessageBox.Show() method in your Script Task.
Guess which one I prefer.
REMEMBER: Make sure you remove these or /* comment them out */ before deploying to production!
Outside Visual Studio
What if you need to see what your code is doing outside of Visual Studio, for example in a SQL Server Agent Job?
Call the Console.WriteLine() method in your Script Task. As long as you choose to Include step output in history for your job, you’ll be able to debug your Script Task by drilling down into SQL Server Job History.
This is a good practice to implement in production as it allows for faster troubleshooting.
You can also fire events in SSIS, but that requires a whole lot of set up and isn’t particularly useful unless you want to trigger other tasks in response, which you could just as easily do with package variables and precedence constraints.
But that’s what’s so cool about SSIS! It has the flexibility to allow many different implementations to achieve the same result. Usually.
By