Redirecting SSIS Data Errors to Avoid Package Failure

When setting up a new Integration Services package, is it often advantageous to leave your errors untrapped so your package crashes and you can interpret the errors. But after you get past the first wave of setup, it is often best to trap for additional data errors in your code to prevent complete package failures.


So how do you do it? I'll cover it in broad strokes.

I start with my source table structure. In order to capture error output, I want to create a table that matches the field names of the source table, but not the data type. The idea is to have a table that is so wide open, it can handle any data you throw at it.

For example, the source table, lets call it T1 has these fields:
ID Int Not Null
Name varchar(30) Not Null
Addr varchar(40) Not Null


To handle error output, you would create a table (T2) with these fields:

ErrCode varchar(100) Null
ErrColumn varchar(100) Null
ID varchar(100) Null
Name varchar(200) Null
Addr varchar(200) Null

Let's go over the noteworthy differences. First I added the ErrCode and ErrColumn fields which will be populated with data from the SSIS error handling.


Most importantly, I changed the data types on all fields to varchar fields and made sure the size was big enough to handle even bad data. Depending on your situation, you may adjust these sizes much larger to accommodate the worst possible over sized data records.


Lastly, I allowed Nulls in all of the columns to ensure that every field of data in the row with errors can be stored in the error table, regardless of Nulls.


Now that the structure is in place, it is time to add to the SSIS package. In the Data Flow where you are pulling from your source table, in our case the nondescript T1 table, we must add the error output. Within the data flow section, drag a destination object from the toolbar that connects to your new T2 table. Then click on the Source object that connects to T1 and drag the red line from the bottom of the object to the T2 Destination box.

If you edit the Error Output in the T1 Data Source, you can specify what should be done with each column of data. You can have a column cause the package to fail on error, ignore the error, or redirect the error output, which is the option we want to use. By redirecting all columns on error, they become available to consume in your T2 table data destination.

Now that the red line is attached, you can edit the properties in your T2 destination to insure that the ErrCode, ErrColumn, and all other fields are mapped properly from your error output to your T2 fields. Once you have the mappings all established, you should be ready to run.

To me the great benefit of this type of error handling comes in dealing with bad data that you are pulling from production databases into secondary aggregate, data warehouse, or reporting databases. It allows you to get most of the data into the primary tables and the leisure to review the bad results afterward. Of course, if the data you are pulling has data requirements that would make partial data useless, you must consider this when considering the error output option. Regardless, using error output is an excellent way to troubleshoot as you are developing a new SSIS package.

SSIS Package Credentials

This is a great article on setting up SSIS Package Credentials, which is more involved than the old DTS days. If you need to schedule your SSIS packages, read this article and it will save you a lot of troubleshooting time.

http://www.sqlservercentral.com/links/81443/131962

Creating a Reporting Service Report Template

I read this good, simple process for creating a template for your company reports that was worth sharing.

http://sqlblogcasts.com/blogs/testas/archive/2009/12/16/creating-a-report-template-in-ssrs-2000-2005-2008.aspx

Netbook upgrade to Windows 7 with USB key

Great tip on how to setup your thumb drive for loading Windows 7. Is it time for a netbook upgrade?

http://technet.microsoft.com/en-us/magazine/dd535816.aspx

Building My First Website Using Microsoft Expressions

In years past, I have used MS Front Page to develop several websites, both personal and as a consultant. In the last few years I have only built ASP.NET web apps until this last weekend. I dipped my toe back into the website design pool using a tool that was new to me, Microsoft Expressions.

Expressions has many similarities to Front Page, but it goes further in mixing the graphical design tools with a light layer of the .NET controls to extend your site's functionality. In my case I just needed to build a basic website without any data interaction, at least at this point. I found the templates, menus, and toolbars to be well laid out and fairly intuitive.

I was able to get up and running and finish the first draft of a full website in around ten hours never having seen the interface ahead of time. To me that is the best indication of a well designed product.

The dynamic web templates allowed me to easily manage the top banner, navigation menu, and side bar separately from the page content on each page throughout the site. I did discover some hangups with the template approach. After developing eight or nine pages, I realized I needed to add a new editable zone to the template. I quickly discovered that this is a major issue once you have already applied your template to one or more web page.

Overall I give Expressions Web a big thumbs up as an excellent web development tool, and I look forward to using more of the functionality over time.

Conditionally Blocking Scheduled Reporting

As is true for many DBAs, I have developed a custom set of SSIS packages along with a database that manages scheduled Reporting Services reports. It delivers to a mixture of email recipients and FTP locations after generating the output PDFs and Excel results.

Then came the inevitable weekend of trouble. The set of SQL Server 2005 databases, which provide the data for the reports, failed to load due to trouble with the source legacy systems. However, since the data in the SQL Server databases still contained yesterday's data, the report schedule had no problem completing the scheduled reporting.

Therein lay the problem. I did not want any reports to generate, or worse yet email to recipients, if the recent day of data was not included.

The solution is fairly simple. The SSIS packages that load the SQL Server databases were logging to a table, and included a special status column. When each database was finished loading and had completed any subsequent updates required, a log entry with a status of DONE and UPDATED were entered. I just created a stored procedure to scan the log for databases that were scheduled to be loaded, filtered by UPDATED, and with a log time that fit into the correct date/time range for the current day's data load.

If the stored procedure found any records that did not meet the above criteria, it returned a result reporting accordingly. Then I just added a call to this stored procedure in my primary scheduled reporting stored procedure, where the list of scheduled reports to process is generated. If the data load check reported problems, I prevent the scheduled reports from processing. Then once the problem with the data load is resolved and all data is available, all I have to do it run the SQL Agent job that runs the scheduled reports and this time it will succeed.

I'm always a fan of a little more work in the back end to make a little less work for the routine. Not to mention, you don't have to answer Fred's email asking, "Why did I get two emails of the same report, and which one is right?"

A New Tablet Computer

This is a super cool new kind of Tablet computing on the horizon. I can easily see how this could change the delivery mechanism for text books on college campuses in time, besides the obvious business uses.

http://gizmodo.com/5365299/courier-first-details-of-microsofts-secret-tablet