An interesting question came up in a recent class regarding testing SSIS in Visual Studio: "How can we capture the information in the Progress tab after a debug session?". My immediate reaction was to implement package logging but the answer lay elsewhere...
Logging in a package can be implemented by choosing Logging from the SSIS menu. Here you can specify various logging providers including text-based, SQL table-based, XML, Windows Event log etc. Then, using the Detail tab, you can specify exactly which events will be logged. The Advanced view lets you decide exactly what is captured when the events occur such as Computer name, username, task name etc. The nice thing about this type of logging is that it will occur no matter how or where you execute the package.
But it's not exactly the same as the information in the Progress tab in Visual Studio. And right-clicking in the Progress tab only allows us to copy each statement individually. This is useful for an individual error, but not to capture the entire Progress of the package. And trying to highlight all the text and copy to the clipboard will not work either.
The answer came from DTEXEC at the command-line. There is a switch called /Reporting or /Rep for short. Using the P option will capture the Progress to the console. Using the pipe character ">" will allow you to pipe to a text file.
For example: DTEXEC /F ssisexample.dtsx /Rep P > progress.txt
Then open progress.txt in notepad to see the results exactly as displayed in Visual Studio.
Who knew? Books Online, that's who.
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.