Focusing on setup and development using SQL Server products from 2000 to 2008 and beyond. Also about anything geeky that compels me to write a blog entry.

Thursday, November 4, 2010

Send Email in SQL 2008 Express

No Email functionality in SQL Express? No problem. This is a nice article on using CLR to send email in SQL 2008 Express.
http://www.sqlservercentral.com/articles/SQL+Express+2008/71341/

Friday, April 23, 2010

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

Sunday, March 28, 2010

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.

Monday, December 28, 2009

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.

Tuesday, December 22, 2009

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

Friday, December 18, 2009

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

Friday, December 11, 2009

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