Today’s post is from Da Lin – a tester on the SQL Server Integration Services team.
-------------------------------------
With the built-in reports and TSQL views , Integration Services in SQL Server “Denali” allows you easily identify the root cause of the execution failure. In the following example, the execution failed because the server name was incorrect. This post will guide you through how to use these built-in features to find the problem.
Once you find out the execution failed, you go to the failed package and launch the Package Execution Report which will show you execution history of this package.
Then you click the “Overview” of the most recent failed execution and see a report showing an overview of this execution. The values of parameters used in the execution will show in the report so that you can examine if the values are set correctly. In this case because the property of the connection manager is parameterized , you can see the server name used in connection manager in the “Parameter Used” section and it was set to an invalid server name in the red box.
If you want to see the detailed messages of this execution, you can click the “View Messages” link. You can check out all types of messages recorded in this transaction.
For the message whose message type is “OnError” you can even drill down to see the context when the error occurred by clicking the “View Context” link.
In this report, you can see the values of the properties when error occurred.
The values of the parameters of the execution you saw in the overview page of the report can also be found in the view [Catalog].[ execution_parameter_values] in SSISDB. By providing the execution id you can query the view to retrieve the values of parameters used during the execution.
