Database Reference
In-Depth Information
Monitoring Execution
Most experienced business intelligence developers will tell you to start with the reports
and work your way back to the source data. The source data in this particular case is in-
formation collected from the data integration process. What kind of information? Th-
ings like start and end execution times, execution status, and error and event messages.
Instance data is recorded for each SSIS application and SSIS package execution.
Each entry represents an execution, and there are two tables that hold these entries:
Log.SSISAppInstance holds execution metrics about SSIS application instances,
and Log.SSISPkgInstance holds execution metrics for SSIS child package in-
stances. When an SSIS application starts, a row is inserted into the
log.SSISAppInstance table. When the SSIS application completes, the row is
updated. log.SSISPkgInstance works the same way for each SSIS package in an
SSIS application. An SSIS application instance is logically comprised of an application
ID and a start time. An SSIS package instance is comprised of an application instance
ID, application package ID, and a start time.
Error and event logging is relatively straightforward. You store a description of the
error or event, the time it occurred, and the instance IDs. That's what the reports will
reflect, and that's all there is to logging.
Building Application Instance Logging
Let's return to SSMS to build the tables and stored procedures to support logging. You
need to execute the T-SQL script shown in Listing A-12 to build the instance tables and
stored procedures.
Listing A-12 . Building the Application Instance Tables and Stored Procedures
/* log schema */
If Not Exists(Select name
From sys.schemas
Where name = 'log')
begin
print 'Creating log schema'
declare @sql varchar(100) = 'Create Schema [log]'
exec(@sql)
 
 
Search WWH ::




Custom Search