Biml and SSIS Frameworks - SQL Server Integration Services Design Patterns - page 543

as long as you realize you can mostly ignore the error codes. The error descriptions,
however, are mostly good. So it balances out.
Before I demonstrate how to capture error messages in SSIS, let's discuss why. I
used to manage a team of data integration developers. The team ranged in size from 28
to 40 developers, and I built very large ETL solutions for US state government in-
terests. Part of my job was to figure out best practices. Having all SSIS packages log
error data in the same format to the same location is a best practice. But how do you do
this with 40 developers? Have you ever tried to get 40 developers to do the same thing
the same way? It's like herding cats. The problem was half of them thought they were
smarter than me; and half of those were correct in thinking that. But this wasn't the
kind of problem that required deep thinking; this required strategy. So what's the best
strategy for getting every developer to build the exact same kind of log for every SSIS
package every time? You guessed it: don't let them. Take error logging completely out
of their hands.
Soon after learning how to use the Execute Package task, I learned events “bubble”
from child to parent packages. For the purposes of error logging, this means I can cap-
ture and record any error at the parent package. Even better, it means I can do this with
no code in the child package . Problem solved.
Let's take a look at how to implement this functionality into an SSIS framework.
First, let's add a table and a stored procedure to record and preserve errors, as shown in
Listing A-16 .
Listing A-16 . Building the Error Logging Table and Stored Procedure
/* log.SSISErrors table */
If Not Exists(Select s.name + '.' + t.name
From sys.tables t
Join sys.schemas s
On s.schema_id = t.schema_id
Where s.name = 'log'
And t.name = 'SSISErrors')
begin
print 'Creating log.SSISErrors table'
Create Table [log].SSISErrors
(
ID int identity(1,1)
Constraint PK_SSISErrors Primary Key Clustered
,AppInstanceID int Not Null