It has been well-documented through myriad blogs and forum posts about the case sensitivity of the comparisons in the SSIS lookup transformation (a good review can be found here). In a nutshell, a comparison using the lookup transformation is case sensitive when using the default setting of Full Cache, even if the values in the database are stored in a case…
Recently, my friend Jack Corbett asked a question on Twitter: In a nutshell, the SSIS foreach loop will enumerate a given list of items (files in a directory, nodes in an XML file, static list of values, etc.) and will perform some operation for each of the items in the collection. This behavior is similar to foreach loop constructs that…
In a couple of previous posts (Part One and Part Two), I shared some thoughts about starting a career path as a database professional, including some notes from my own journey. In this final segment, I’ll share a few discrete tips that will hopefully be useful to those interested in this field. Build Your Foundation: You’re going to need some…
Working with an ETL design that does a straight insert operation is the most simple, but often ETL processes are required to update data as well. There are several ways to go about updating data with SSIS, one of which (the staging table method) requires a bit more setup but can also perform significantly better. Updating Data with SSIS I…
I ran into a situation this week that brought to light a subtle syntactical error I’d made in creating an SSIS package. I’ve got a client that has given me access to their development server to create some complex extraction queries, which will eventually be rolled into SSIS packages. Since I’m working with read-only access and cannot create stored procedures…
For SSIS developers, the need for proper documentation is crucial. However, the built-in object for documentation, the annotation, is difficult to use. It doesn’t wrap text, doesn’t support varying font styles in a single instance, and doesn’t offer spell checking. Further, all annotations are “at large” and are not attached to a particular object – they are associated with a…
In a previous blog post, I began writing about how one might start a career as a database professional, and agreed to share my own experience in doing so. Since every person and every situation is different, I don’t declare my experience to be a recipe for success, but I’m happy to share it in hopes that someone can learn…
I read a post on Brent Ozar’s blog last week that discussed employers’ expectations when hiring new team members. Though the story was specific to database professionals, the same principles apply to almost any hiring situation. The moral of Brent’s story is that when hiring, just like in real life, you have to compromise what you may really want to stay within…
Have you ever noticed unexpected gaps in sequences in SQL Server identity columns? Even though you’ve got transactions set up for your inserts and a no-deletion policy, you find that there are missing values in what should be an unbroken sequence of numbers. The problem could be partially related to transaction rollbacks. Conventional wisdom would lead one to believe that…
Should you ever delete data? Data quality is important, and completeness is a measure of the quality of data. Whether you refer to it as data integrity, permanent retention, or simply maintaining a complete audit trail, it can be effectively argued that purging old data clouds the big picture that the data presents. After all, any data that is worthy…