Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to serialize error logging in SSIS


By:   |   Last Updated: 2010-03-22   |   Comments (4)   |   Related Tips: 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.

 Create a new SSIS project and name it RnDR2
 

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.

Create a Derived Column Transformation
 

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.

create a simple error table, where we should be able to track the source and values of the error records
 

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.

add a OLE DB Destination adapter to record the error output to our error table
 

7) Our package should look like the screenshot below after following the previously mentioned steps.

Our package should look like the screenshot below
 

8) Execute the package and this should insert rows into the our error table as shown in the screenshot below.

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.

Tailor this table as per your own specific needs.
Next Steps


Last Updated: 2010-03-22


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, February 13, 2013 - 4:01:13 PM - Salaam Back To Top

 

Must say, nice tutorial but badly writen! you never mention anything about parameters! have you got a package to download?


Thursday, September 23, 2010 - 2:59:52 PM - Sue Chang Back To Top

I follow your instructions step by step. Up to step 6, I am ready to record Error Records. However, I am unable to map input columns to destination columns. Because I do not see these four params in destination columns. Do you have any idea what I am missing? Thanks.


Friday, May 07, 2010 - 5:59:43 AM - sudeep347 Back To Top

Really nice way to save errors and easy to retrieve the details. Like it.


Monday, March 29, 2010 - 1:17:05 AM - Mentos Back To Top

What is the point of this article?


Learn more about SQL Server tools