Using Extended Properties for Documentation

This is a nice article on using extended properties to document your database. Much easier than maintaining a Word document.

Create a SQL Server Data Dictionary in Seconds using Extended Properties

SQL Saturday Free Training

I just attended my first free SQL Saturday training day (#29 in Birmingham, AL) and it was an amazing day. The training was of the highest quality and as I went through the day with free coffee/soda and free lunch provided, I could not believe all of the value that was being provided for free. Of course it is not free, just free to we the trainees.

A big thank you to all the sponsors and the volunteers that made the day possible.

I want to encourage all of you reading this to go to the SQL Saturday website and find SQL Saturday's in your region. There were many people that I met in sessions who were from Tennessee and Georgia, and some had been to as many as three SQL Saturday's in different locations. It is worth a little driving time.

The hardest part was choosing which sessions to attend, since it was pretty common to have two or more sessions that I was interested in going at the same time. Here is the list that I finally went with.

Parallel Query Execution Dive Deep presented by Kevin Boles.
Database Design Patterns presented by Louis Davidson.
Tuna Helper for SQL Server DBAs presented by Janis Griffin.
Tricks & Tips for Writing Better Queries presented by Joe Webb.
SQL Server Locking and Blocking Made Simple presented by Joe Webb.

In each session, I already knew some of the material and I learned some new things, and most importantly gained some insight from people who approached problems from different angles. SQL Server is so robust, there are hundreds of ways to carve the turkey.

I hope if you have never attended SQL Saturday that you will make it a priority. All it costs you is one Saturday.

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.