SSIS Logging in SQL Server 2012

 

Enabling SSIS logging is a tedious process in earlier versions. Integration Services Packages has the two options to log the information during the execution, one option is configure the log providers in each package and associate them to executables within the package. Second option is to use SQL statements or script components to implement a custom logging solution.

With the introduction of Catalog in integration services in SQL Server 2012 , package execution data is stored in [catalog].[executions] table. You will have start and end time columns of package execution in this table. You no need to configure anything to store this data in this table. Catalog database also contains the information on Integration Services environment like physical memory, page file size and available CPUs.

You can write queries on these table in Catalog database and build custom reports using reporting services for monitoring the SSIS log files.

Thorough information on various tables where information is stored can be found here

Data Taps

Data Taps can also be used for capturing the SSIS execution, it is similar to the concept of data viewer. Data Taps captures the data at specific point in the pipeline during the package execution. The captured data is stored in CSV file where you can review after package execution is complete.

Out of the Box Reports 

Built in reports are available in SQL Server Management Studio on Package Execution tables. These reports provide information on package execution results for the past 24 hours. It also provide hyper link where you drill through from summary to detailed information to diagnosis the package execution problems.

To view the above report, Right-Click  the SSISDB node in object explorer, point to reports then standard reports then you can choose any of the below available reports

• All Executions
• All Validations
• All Operations

Share this post :