Brian Knight posted on November 08, 2005 02:06
Created with Beta 2
In the last tutorial, I showed you how to create your first data transformation in SQL Server 2005 using SSIS. In this tutorial, we’re going to expand the last tutorial to add additional error handling to trap bad data from our source flat file. If you have not gone through the last tutorial, please do so you’ll be caught up with the basics. If you wish not to do that, you can download the last tutorial’s code to catch up in our download section. There is a download in our downloads section for this tutorial as well.
I actually encountered the error we’re going to be discussing while trying to produce the first tutorial. I had set the data of the quantity column to be a 2-byte integer, which cannot handle the summation of a few of the rows in our sample input file. This caused an error that became quite frustrating to troubleshoot so it sparked me to add the error handling we’re about to discuss. After the very basic error handling was in place, the rows were transformed into the destination and the two bad rows, were outputted to an additional flat file with the error number. If you didn’t do this, then the entire package would fail.
It’s important to note that SSIS in SQL Server 2005 has lots of ways to handle error handling. We’re just going to explore one of those in this tutorial but I’ll surely expand this at a later date. Before we begin, let me refresh you want the SSIS package looked like from our last tutorial. The transformation essentially converted a 90,000 record flat file into a summary file that aggregated and grouped all the data by product. Here’s a screenshot of what mine looked like once it was executed:
