Today I had to write a little function that takes the long had version of a fraction, say 8 3/4 and convert it a proper number (float) and then convert that number from inches to centimeters. While that is handy, I thought it would be best to include a few… Read more

There are countless ways to mix and match components in SSIS to simulate what SQL Server does in a query.  Here I am going to show you how to replicate an existence check using SSIS components.  The only time I use this type of configuration is when I have cross-server… Read more

During my SSWUG webcast I was asked to give some examples on how to tune the DefaultBufferMaxRows and DefaultBufferSize, so here goes.

When a source is pulling in data, it places them into buffers; one row=one buffer.  The default setting for DefaultBufferMaxRows=10,000.  This means that the source will… Read more

I've been asked on several occassions to provide an example of the custom sources I use in my "SSIS Tips&Tricks" presentations.  I had to build one moments ago and realized I am tired of looking back through my presentations to build them.  Here is a sample script that you can… Read more

I built a small ruby program a while back to help with two things:

1. Quickly identify what tables have the highest IO in a particular query
2. Learn a new language (Ruby)

The problem was simple.  I needed to easily identify the problem areas of a complex stored procedure.… Read more

The other day I was building a simple data import package when I ran into a common situation; invalid conversions in a Derived Column.  The conversion was taking an ISBN13 which is usually defined as char(13) and converting it into a bigint.  All valid ISBN13s are valid bigint.… Read more

I've been doing quite a bit of work for my upcoming SQL University class next and in it is a Script Component.  These are the ones that live inside of a Data Flow task, which means: "No Breakpoints!".  What a pain that can be.  I had red boxes being… Read more

While designing a package last week with our consultant, Latha Chandri (twitter), I came across a need to call a stored procedure drom within a Data Flow Task.  I have never needed to do this before and while the call is simple there are a few quirks… Read more

Here I am working on tuning a rather large stored procedure; old school.  I have statistics IO and showplan turned on.  I work through the obvious missing indexes and improper joins and now I'm into the 'Let's try this technique', to improve different pieces.  The gauge for me… Read more

We have a table that contains just over 1.7 billion records and requires a daily feeding of around 9 million.  It is split into 10, roughly equal, partitions.  I originally just pushed the daily feeding into the main table from a staging table and it worked well, but thought to… Read more

I had the privilege of giving a SQL Lunch presentation today.  The slide deck for that can be found here: SSIS Tips & Tricks deck.  The recording can be downloaded at SQLLunch.  I am not sure when it will be available, but when it is I'll see about… Read more

I wanted simply give a shout out to Andy Leonard (twitter | blog) for his never ending commitment to the SQL Community.  Take a look at SQLPeople.net and join the community. Read more

It's not everyday that one of your information vendors decides to change the format of its data feed.  Evidently, today is not every day.  It would be one thing to change the data type of an incoming field or even add/remove a field from the feed.  Nope, they decided to… Read more

Currently, none of the existing Data Flow sources allow you to source variables.  Once again in my quest for performance, I look to keep as much data as possible in memory.  There are many uses for pushing data to external files (Raw Files, control files, etc), but there is a… Read more

I developed an SSIS package that loads invoices, statements, pro-formas, etc. into a SQL Server table.  The invoices are .pdf files and the business required that no identical documents were allowed to be stored.  To determine uniqueness I simply created a hash of each document and used it as the… Read more

Since I designed the Enhanced Threading Framework (ETF) I am always looking to improve it.  Status quo is not for me.  I decided to take on the 'queue' part of the framework.  This is the part that uses a SQL Server table to manage the list of work to be… Read more

I've been toying with the idea of using a file as a queue instead of a database table for use with my Enhanced Threading Framework.  This has been challenging and I am nearly complete.  I can't say at this time if it is faster or slower, better or worse, a… Read more

I have gotten a number of emails over the past few days asking about how I import binary files into SSIS as well as how to improve throughput by making tasks parallel.  I have, scattered throughout this blog, articles which show bits and pieces.  I have articles on how…

Read more

I heard of EverNote sporadically in the Twitter community and from my boss.  I had checked it out and it seemed like another place to store notes/tips/ideas.  I thought to myself 'What could I possibly use this for?"  I've read some articles where a restaurant chef uses this to… Read more

I had a very interesting bug that had me running around for over a week.  Turns out the bug was in my code due to a misunderstanding of how SSIS processes the  Data Flow Task.   I've found in that time that SSIS not only evaluates, but actually 'runs' code… Read more