Integration Services Error Handling Tips

In an ETL solution, error logging is a fundamental requirement for various aspects of the application and is very useful at various stages of the ETL execution life-cycle. Let's consider a scenario where our requirement is to insert records into a SQL Server table. The package should attempt loading all the records and whichever records fail, error details reported by the database engine should be reported. We will look at how to implement this in a SSIS package.

We have quite a few SSIS packages that we run on a regular basis to extract data from our OLTP systems and transform/load into our data warehouse. We would like to capture some basic information about these SSIS packages such as what packages were run, when, how long did they take to run, and how many rows were extracted and loaded. We have looked in to the built-in logging that comes with SSIS but we just don't see a straight forward way to get row counts. Are we missing something?

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.

We have quite a few SSIS packages that we run on a regular basis to extract data from our OLTP systems and transform/load into our data warehouse. We would like to capture some basic information about these SSIS packages such as what packages were run, when, how long did they take to run, and how many rows were extracted and loaded. We have looked in to the built-in logging that comes with SSIS but we just don't see a straight forward way to get row counts. Are we missing something?

In an ETL solution, error logging is a fundamental requirement for various aspects of the application and is very useful at various stages of the ETL execution life-cycle. Let's consider a scenario where our requirement is to insert records into a SQL Server table. The package should attempt loading all the records and whichever records fail, error details reported by the database engine should be reported. We will look at how to implement this in a SSIS package.

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.

In an ETL solution, error logging is a fundamental requirement for various aspects of the application and is very useful at various stages of the ETL execution life-cycle. Let's consider a scenario where our requirement is to insert records into a SQL Server table. The package should attempt loading all the records and whichever records fail, error details reported by the database engine should be reported. We will look at how to implement this in a SSIS package.

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.

We have quite a few SSIS packages that we run on a regular basis to extract data from our OLTP systems and transform/load into our data warehouse. We would like to capture some basic information about these SSIS packages such as what packages were run, when, how long did they take to run, and how many rows were extracted and loaded. We have looked in to the built-in logging that comes with SSIS but we just don't see a straight forward way to get row counts. Are we missing something?