SSIS - search or filter Execution Results in Debug Mode

Question

I have a .dtsx package where I load huge number of flat files in thread-like loops.

In every loop, if there's something wrong with the file, the process copies the file to another location and continues with execution. Now, I'd like to search Execution Results tab after the package is finished, and see what went wrong for those files. But there's a huge number of entries, and there's no search option!

Does anybody know kow to search in Execution Results tab, filter errors only, or to save entire Execution Results output to a text file? (I know I could be using Event Handlers and custom logging, but that's not my issue here).

Thanks in advance for any hints.

Answer 1

You can enable the Inbuilt Logging Feature and select SQL Server as Provider Type .SSIS execution will be logged in a table named sysdtsLog90(SSIS 2005 in MSDB database) or sysssislog (SSIS 2008).

Select the details which needs to be logged in the Database

Now when you execute your package u can find the information iof SSIS package execution details in the table .

In order to find the task which got failed ,you can just query the table

 select EVENT,source,starttime,endtime 
 from sysssislog 
 where EVENT like '%OnTask%'or EVENT like '%error%' 

Of you can use a 3rd party tool BIDS HELPER to visualize the SSIS Performance