Database Reference
In-Depth Information
,@SourceName varchar(255)
,@ErrorDescription varchar(max)
As
insert into log.SSISErrors
(AppInstanceID, PkgInstanceID, SourceName,
ErrorDescription)
Values
(@AppInstanceID
,@PkgInstanceID
,@SourceName
,@ErrorDescription)
go
print 'Log.LogError stored procedure created.'
print ''
Each row in the
log.SSISErrors
table contains an
AppInstanceID
and
PkgInstanceID
for identification purposes. Why both? It is designed to capture and
preserve errors that originate in both the parent and child packages. An error in the
Parent.dtsx
package will have a
PkgInstanceID
of
0
. The remaining columns
capture metadata about the error proper: the date and time the error occurred (
Er-
rorDateTime
), the error message (
ErrorDescription
), and the SSIS task from
which the error originated (
SourceName
).
Caution
Adding a row to the
log.SSISErrors
table with a
PkgInstanceID
of
0
will actually raise a foreign key constraint violation at this time, but I will address
this matter later in the appendix.
It is important to note that error events are “raised” by SSIS tasks. When an error
event is instantiated, its fields are populated with information such as the error descrip-
tion and source name (the name of the task raising the error). These data do not change
as the event navigates, or bubbles, inside the SSIS package execution stack. When the
event arrives at the
Parent.dtsx
package in the framework, it will contain the name
of the task that originated the error (
SourceName
) and the description of the error
from that task (
ErrorDescription
).
When the error bubbles to the
Parent.dtsx
package, you will call the
log.LogError
stored procedure to populate the
log.SSISErrors
table. In
Search WWH ::
Custom Search