By: Siddharth Mehta | Comments (4) | Related: More > Integration Services Error Handling
Problem
Error handling is one of the aspects of SSIS that is given quite low attention in terms of recording error data at different stages of the transformation. One of the reasons for this is that at different stages of the transformation, the metadata stream keeps on changing it's shape. One needs a generic error data repository where error data can be recorded in a generic storage repository, which is easy to query and preserves the recorded elements. Let's see how we can serialize error output and query the data.
Solution
Before we start discussing our example to demonstrate the way to deal with the above mentioned problem in question, I would like to share a few of my thoughts on Serialization. What is Serialization ? This is a very commonly used term in the application programming world like .NET, but we hear it quite less often in SSIS. Serialization refers to the process of converting an object into a generic or compatible form to facilitate mobilizing of the object into different environments, and the reverse process is called de-serialization which means converting back from serialized form to it's original form.
Please keep in view that Serialization and De-Serialization are the logical terms and I am using it to explain the model that I am proposing in this tip. Do not confuse it with any official concept released by Microsoft in SSIS, as these terms and concept are a part of the architecture model that I am attempting to propose by the means of this tip.
Let's start with our example. This tip will assume a basic working level understanding of SSIS from the reader. In our example, we need the AdventureWorks sample database. Follow the below steps to create our package and example.
1) Create a new SSIS project and name it RnDR2. Add a new package to it and name it "Error Handling Module."
2) Create a new connection to the AdventureWorks database. We go with the assumption that before trying this example, you would have already installed the AdventureWords database on your SQL Server.
3) Create an OLE DB Source Adapter and use the above create connection. Connect to the "HumanResources.Employee" table. Now we need to tweak this to get some records in the error output so that we can use them for our example. Change the length of the "Title" column to "5" as shown in the screenshot below using the Advanced Editor. Also configure Error Output of this control to redirect rows for all the columns on error and truncation.
4) Create a Derived Column Transformation, name it "Serialize Error Record" and create two new columns as shown in the below screenshot. Our intention is to record some column values of the error output and store it in the error table that we would create in the next steps.
5) For our storage let's create a simple error table, where we should be able to track the source and values of the error records. Create a table called dbo.ErrTable by using the script shown in the below screenshot.
6) Come back to our package, add a OLE DB Destination adapter to record the error output to our error table. Name it "Report Error Records" and configure it as shown in the screenshot below.
7) Our package should look like the screenshot below after following the previously mentioned steps.
8) Execute the package and this should insert rows into the our error table as shown in the screenshot below.
9) I call the value in "ErrValue" column as the serialized error record. Now we should be able to query the columns values that we serialized in the "ErrValue" column. This is the reason why this column was created with "XML" datatype and not "varchar". See the below screenshot how we de-serialize this values and query the column values we serialized in this column like any normal table.
Of course there are multiple ways to query the data, but this is one of the ways to query our serialized data. Also this error table would be able to accommodate any level of column values and the only change required would be the query values and XPath used in those query values. Again it's not the most broadest and most generic level of capturing error data, but for our example it holds good, our concept gets demonstrated and our issue gets solved. Tailor this table as per your own specific needs.
Next Steps
- Download and install the AdventureWorks database to use as a sample.
- Try to implement this error reporting mechanism with other transforms.
- Check out these related tips:
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
