How to capture your Progress in SSIS...

SQL Server

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.

http://msdn.microsoft.com/en-us/library/ms162810.aspx

cheers
Brian

• Microsoft
• debug
• integration services
• progress
• SQL Server
• SQL Server 2008
• SSIS
• ssis logging