Roll your own error-handler in SSIS
I was working on error handling with SSIS recently and came up with what I believe is a snappy way to address it. The solution presented below is partial. For one, I would encourage you to use a database table to house errors; for another, I would encourage you to store lots more data than merely the ErrorDescription field.
Error logging is crucial to troubleshooting and auditing efforts. If an error occurs in your SSIS package while it is executing production loads, transformations, or transfers; you will want to know as much as possible about it.
To demonstrate, create a new SSIS project named ErrorTest:
I encourage you to develop the practice of renaming packages as you create them. This will make your life simpler once you deploy:
I rename Package.dtsx to ErrorTestPkg.dtsx (don't change the extension):
When you rename a package the following dialog will appear:
Always answer this dialog "Yes".
Drag an Execute SQL Task onto the Control Flow:
Configure it (double-click or right-click and select Edit to open the editor) as shown below. Note any connection will suffice, so long as the SQL Server is running. Also note the SQL will generate an exception (intentionally):
Click OK to close the editor. Right-click the Task and select Execute Task to test:
The task should fail (as planned):
Stop execution and click on the Event Handlers tab:
Check to be sure the Executable dropdown is set to the ErrorTestPkg (the package) and the Event handler dropdown is set to OnError. Click the link to create a new OnError Event Handler for the package. From the Toolbox, drag and drop a Data Flow Task onto the Event Handler:
Double-click the Data Flow Task to edit it. From the toolbox, drag a Script Component transformation onto the Data Flow:
The Script Component can be configured as a Source, Transformation, or Destination. Select Source:
Since a Source has only outputs (a destination has only inputs, and a transformation has both inputs and outputs), you will need to configure the Output. Rename the generic Output 0 to ErrorOutput:
Click on Output Columns and click Add Column:
Rename the Column ErrDescription:
Edit the ErrDesription column's properties - change the datatype to String (DT_STR) and the length to 5000:
Add the ErrorDescription System variable to the ReadOnlyVariables property of the Script Component:
There are other variables you will add to your error handling script component. Remember variable names are case-sensitive, comma-separated, with no spaces in the list.
Click the Design Script button and add the following code snippet as shown below:
With ErrorOutputBuffer
.AddRow()
.ErrDescription = Me.Variables.ErrorDescription
End With
Some explanation: The name of the ErrorOutputBuffer was determined by the name you gave the Script Component Output earlier. When you changed the Output name to ErrorOutput, the script component's output buffer was changed to ErrorOutputBuffer.
To populate the buffer, you must add rows to it. Think of this as starting a new row in a table. If you're familiar with tables in Microsoft Word documents, you know when you start a new row it is empty until you populate it. The same is true here. You are creating a new, empty row in the output buffer to populate with data.
The final step is to load the row. Again, in your production-ready error event handler you will populate more columns in the output buffer than this one - this is for demonstration purposes only:
Next, drag a flat file destination onto the Data Flow and connect it to the Script Component as shown:
Note: You will likely want to store error data in a database rather than a flat file.
When you configure a flat file destination, you are prompted for which type of file. Delimited files are fairly simple to read - especially with few fields:
Select a location and name for your flat file. I chose C:\ErrorOutput1.txt:
Click Columns to view the columns the file will contain. Note: the columns would be empty if you had not connected a data path from the Script Component prior to configuring the Flat File Destination:
Click OK to proceed. Click the Mappings page to assign mappings between fields in the pipeline and fields in the destination:
Since you built the destination from the metadata contained in the pipeline, auto-mapping should occur when you click Mappings:
Click OK. Your Event Handler should appear as shown:
Start debugging the package by pressing F5 or clicking the Debug Run arow. Your ExecuteSQL task should fail as before:
Click on the Event Handlers tab to determine the run status of your Package OnError Event Handler. Double-click the Data Flow Task and note one row was written to your error file (ErrorOutput1.txt):
Open ErrorOutput1.txt file to view the error stored there:
In this example, we built a package to demonstrate logging package errors to a data destination - albeit a flat file. This technique can be used to catch errors and store them for troubleshooting posterity.
:{> Andy
Technorati Tags: SSIS OnError Event Handlers Logging