Eric Johnson

The Trouble with SSIS Sorting

ejohnson2010 — Wed, 03 Feb 2010 14:00:00 GMT

Many SSIS transformations, such as the Pivot and the Data Profiling Tasks, require that the data being fed into them be sorted. Without first sorting, some transformations will throw an error and not run, while others will run but the results will not...(read more)

Understanding the SSIS Package Protection Level

ejohnson2010 — Tue, 12 Jan 2010 17:46:05 GMT

One property of all SSIS packages that you must understand is the ProtectionLevel. This property tells SSIS how to handle sensitive information stored within your packages. Most commonly this is a password stored in a connection string. Why is this information...(read more)

Troubleshooting a Failed Maintenance Plan

ejohnson2010 — Wed, 23 Dec 2009 20:20:40 GMT

I recently ran into an odd little problem with a Maintenance Plan that I wanted to share. I had a plan that was running the Check Database Integrity Task that suddenly started failing. Nothing about the databases had changed and if you ran a manually...(read more)

Check Out My SSIS Screen Casts

ejohnson2010 — Mon, 14 Dec 2009 22:16:10 GMT

Two I did for TechTarget: Using the Pivot Transformation in SQL Server Integration Services: http://searchsqlserver.techtarget.com/video/0,297151,sid87_gci1374920,00.html Using package configurations in SQL Server Integration Services: http://searchsqlserver.techtarget.com/video/0,297151,sid87_gci1369995,00.html...(read more)

SSIS Package Builds

ejohnson2010 — Tue, 08 Dec 2009 20:29:18 GMT

So you have written an SSIS package and now its time to package it up and send it off to your QA department. Excellent; you need to use the build function in Business Intelligence Development Studio. On the surface this is pretty simply but there are...(read more)

SSIS 2008: Looping Through Rows in a Table

ejohnson2010 — Fri, 13 Nov 2009 22:09:06 GMT

Inevitably when writing code, you have a need to write a loop in order to iterate over multiple objects. When writing code against a SQL Server, as we usually are doing in SSIS Packages, you often need to iterate over all the rows in a table. This can...(read more)

SQL Server 2008’s New Import and Export Wizard

ejohnson2010 — Mon, 19 Oct 2009 15:58:06 GMT

I have written a lot lately about SSIS package development, and that doesn’t apply to everyone that works with SQL Server 2008. So you might be asking, what’s does SSIS have to offer me, the production support DBA? Well, I am glad you asked. In SQL Server...(read more)

Working with SSIS Expressions

ejohnson2010 — Mon, 12 Oct 2009 16:47:38 GMT

In SQL Server Integration Services (SSIS) Packages, expressions are everywhere. You may have worked with Expressions in some of the transformation tasks, such as the Derive Column Transformation, but did you know they can also be used to set the properties...(read more)

Managing Lookup Cache in SQL Server 2008

ejohnson2010 — Tue, 29 Sep 2009 19:59:30 GMT

The Lookup transformation in SSIS has changed a lot in SQL Server 2008. One of the best new features is the ability to pre-build your cache which gives you a lot of control over what is cached and how the cached data is managed. The basic lookup offers...(read more)

Using SSIS Package Configurations

ejohnson2010 — Mon, 21 Sep 2009 15:55:37 GMT

SQL Server Integration Services (SSIS) is a very powerful tool for creating ETL Packages. Part of what makes it so powerful is its ability to use package configurations. Package configurations allow you to externally store information that the package...(read more)

SSIS 2008: Data Profiling Task

ejohnson2010 — Fri, 11 Sep 2009 21:30:34 GMT

SQL Server 2008 Integration Services shipped with the wonderful new Data Profiling Task. This task, much as the name implies, will profile the data in a given table and return a ton of useful information. The task gathers statistics for candidate keys,...(read more)

File and Filegroup Space Details

ejohnson2010 — Fri, 07 Aug 2009 16:34:00 GMT

Here is a little SQL Script I wrote that gives you file and filegroup size details for all databases on SQL Server 2005 or 2008. This returns all the vital information I am looking for when I look at database sizes and growth such as file size, space...(read more)

SSIS 2008 and the New Lookup

ejohnson2010 — Wed, 01 Jul 2009 21:33:44 GMT

SSIS is a pretty useful tool for designing ETL processes. One of the transformations I was disappointed with in 2005 was the lookup. I found it a little better than the lookup functionality in DTS, but not too much better. Well, Microsoft must have read...(read more)

SSIS Connection Wizard

ejohnson2010 — Tue, 09 Jun 2009 16:40:31 GMT

Just found a little something new in SSIS 2008, The Integration Services Connections Project Wizard. This thing starts up when you first begin a new SSIS project. The whole point is to walk you though your initial connection configurations. Once the connections are set up, it even asks you if each is a source, destination, or both and then it creates your first Data Flow task complete with the correct sources and destinations. This probably isn't all that useful to someone that has spent any measurable amount of time with SSIS, but for new comers I think its great. I remember the first time I opened SSIS, it took me a minute to figure out how to get a connection , a source, and a destination all set up. Granted I tend not to read documentation, so I am probably a special case.

In any event, if this pops up in your face the next time you start a project. Have a look, it’s a neat little addition.

Slowly Changing Dimensions - Duplicate Data Issues

ejohnson2010 — Tue, 05 May 2009 21:14:23 GMT

The Slowly Changing Dimension (SCD) transformations are pretty handy in SSIS packages. They let you compare a new set of data to an existing table and insert or update as needed. Alternatively, you can have them insert new rows and mark old rows as "Expired" instead of updating rows when a change occurs. This is great if you want to maintain history. Now these are meant for dimensions in a data warehouse, but you can use them against any table in your database. The reason for this post is to look at an issue that can occur with SCDs when you have duplicate data in your incoming data set.

When an SCD analyzes a set of data it looks at each row and compares it to the destination. It decides whether the row will be inserted or updated (or inserted with the old row being marked inactive). Notice I said this comparison is with the incoming data and the destination. The SCD does check for duplicates that may exist in the incoming data. So if you have duplicates, that also happen to be new rows when compared to the destination, all the duplicate rows get "flagged" for insertion. You can probably guess what happens next. The first row gets inserted but the second and subsequent duplicates cause a Primary Key violation when they attempt to insert the now duplicate record.

Now this might seem like a glitch to some, but this functionality is "by design ". The button line is that the data being inserted should be de-duplicated prior to being inserted into the destination. In a perfect world, the data would come from a source where it lived in a normalized, and therefore de-duplicated, state. If you don't have the luxury of living in a perfect world, you can build a manual data de-dup process or use a Sort transformation. One of the options of the Sort transformation is to "Remove Rows with Duplicate Sort Values". Just drop one of these in, check the box, and you have magically de-dup your values.