When you configure an error output in the data flow, by default the error output provides only the numeric identifier of the column in which the error occurred. For more info, see Error Handling in Data.
You can find column names by enabling logging and selecting the DiagnosticEx event. This event writes a data flow lineage map to the log. You can then look up the column name from its identifier in this lineage map. Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log. Perhaps the most useful custom log entry is the PipelineComponentTime event.
This log entry reports the number of milliseconds that each component in the data flow spends on each of the five major processing steps. The following table describes these processing steps. Integration Services developers will recognize these steps as the principal methods of a PipelineComponent. When you enable the PipelineComponentTime event, Integration Services logs one message for each processing step performed by each component.
The following log entries show a subset of the messages that the Integration Services CalculatedColumns package sample logs:. These log entries show that the data flow task spent the most time on the following steps, shown here in descending order:. The Aggregate transformation that is named "Sum Quantity and LineItemTotalCost" spent a combined ms in PrimeOutput and 79 in ProcessInput-performing calculations and passing the data to the next transformation.
This procedure describes how to add logs to a package, configure package-level logging, and save the logging configuration to an XML file. You can add logs only at the package level, but the package does not have to perform logging to enable logging in the containers that the package includes. By default, the containers in the package use the same logging configuration as their parent container.
For information about setting logging options for individual containers, see Configure Logging by Using a Saved Configuration File. Select a log provider in the Provider type list, and then click Add. Depending on the selected provider, use one of the following connection managers:.
For Text files, use a File connection manager. For more information, see File Connection Manager. Optionally, select the package-level check box, select the logs to use for package-level logging, and then click the Details tab.
On the Details tab, select Events to log all log entries, or clear Events to select individual events. On the Details tab, click Save. The Save As dialog box appears. Locate the folder in which to save the logging configuration, type a file name for the new log configuration, and then click Save. To save the updated package, click Save Selected Items on the File menu. Configure the Options in the Containers Pane. Configure the Options on the Providers and Logs Tab. Containers Select the check boxes in the hierarchical view to enable the package and its containers for logging:.
If dimmed, the container uses the logging options of its parent. This option is not available for the package. If a container is dimmed and you want to set logging options on the container, click its check box twice. The first click clears the check box, and the second click selects it, enabling you to choose the log providers to use and select the information to log.
Add Add a log of the specified type to the collection of log providers of the package. The name field is editable. Use the default name for the provider, or type a unique descriptive name. Description The description field is editable.
Click and then modify the default description of the log. The log provider for the Microsoft Windows Event Log requires no connection. Use the Details tab of the Configure SSIS Logs dialog box to specify the events to enable for logging and the information details to log. The information that you select applies to all the log providers in the package. For example, you cannot write some information to the SQL Server instance and different information to a text file.
Advanced Select or clear events to log, and select or clear information to log for each event. Click Basic to hide all logging details, except the list of events. The following information is available for logging:.
Basic Select or clear events to log. This option hides logging details except the list of events. If you select an event, all logging details are selected for the event by default. Click Advanced to show all logging details. Load Specify an existing XML file to use as a template for setting logging options.
This procedure describes how to configure logging for new containers in a package by loading a previously saved logging configuration file. By default, all containers in a package use the same logging configuration as their parent container. For example, the tasks in a Foreach Loop use the same logging configuration as the Foreach Loop. You can create logs only at the package level. Optionally, select a different log entry to log by selecting its check box in the Events column.
Click Advanced to select the type of information to log for this entry. The new container may include additional log entries that are not available for the container originally used to create the logging configuration. These additional log entries must be selected manually if you want them to be logged. This topic describes how to set or change the logging level for a package when you run a package that you have deployed to the Integration Services server.
You can pick from one of the built-in logging levels described in this topic, or you can pick an existing customized logging level. You can also specify the logging level for an individual package by using one of the following methods. This topic covers the first method. Setting parameters for an instance of an execution by using the catalog. Under Logging level , select the logging level. This topic contains a description of available values. The following built-in logging levels are available.
You can also select an existing customized logging level. This topic contains a description of customized logging levels. You can create customized logging levels that collect only the statistics and events that you want.
Optionally you can also capture the context of events, which includes variable values, connection strings, and component properties. When you run a package, you can select a customized logging level wherever you can select a built-in logging level.
To capture the values of package variables, the IncludeInDebugDump property of the variables must be set to True. The Customized Logging Levels list contains all the existing customized logging levels.
To create a new customized logging level, click Create , and then provide a name and description. On the Statistics and Events tabs, select the statistics and events that you want to collect. On the Events tab, optionally select Include Context for individual events. Then click Save. To update an existing customized logging level, select it in the list, reconfigure it, and then click Save. To delete an existing customized logging level, select it in the list, and then click Delete.
All users of the SSISDB database can see customized logging levels and select a customized logging level when they run packages. SQL Server Integration Services provides a rich set of custom events for writing log entries for packages and many tasks. You can use these entries to save detailed information about execution progress, results, and problems by recording predefined events or user-defined messages for later analysis.
It also shows the existing connections to use. If you want to create a new connection, you can provide the required details such as SQL Instance name, database name, and authentication in the connection window.
Click on the Progress tab for the detailed error message. By looking at the following screenshot, we can identify the error message. We want a similar kind of error message every time the package executes in SQL Agent job as well.
You get an additional option, and it allows you to select the additional columns to capture in the SSIS logs. If you have an existing file, you can use usage type as an Existing file. The package is failed again with the same error message.
We can configure multiple SSIS log providers in the same package. Right-click on the control flow area and go to logging again. In SQL Server logs also, we want to capture the error only. For information about how to troubleshoot running packages, see Troubleshooting Tools for Package Execution. When you run an Integration Services package, the package typically logs various messages about the progress and status of the package.
The following table lists those messages. The package will log the messages in the following table even if you have not enabled logging for the package. By default, in a new installation, Integration Services is configured not to log certain events that are related to the running of packages to the Application event log.
However, in an upgrade insallation, Integration Services is configured to log these two events. If you have enabled logging on the package, the Application event log is one of the destinations that is supported by the optional logging features in Integration Services packages.
When you have enabled logging on the package and the log location is the Application event log, the package logs entries that pertain to the following information:. The following table lists only some of the messages that are the result of events.
For a more comprehensive list of error, warning, and informational messages that Integration Services uses, see Integration Services Error and Message Reference. This procedure describes how to run a package and view the log entries it writes. You can view the log entries in real time.
0コメント