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

 I have become accustom to modifying package variables within my Script Task using the VariablesDispenser  object.  It is convenient and powerful.  I started working with this object due to issues I found while hacking my way through SSIS2005.  I would put the variables in the ReadOnly…

Read more

 I have become accustom to modifying package variables within my Script Task using the VariablesDispenser  object.  It is convenient and powerful.  I started working with this object due to issues I found while hacking my way through SSIS2005.  I would put the variables in the ReadOnly…

Read more

I have built an enormous number of packages for this project over the past few weeks (just over 60) and wondered if a conditional split case without a destination was efficient.  I have been using the Conditional Split component with one case going nowhere as a way to 'throw' out… Read more

Views are used quite often within SQL Server for a number of reasons.  They can be used to restrict access to tables (restrict the columns viewable or the records returned) , combine multiple tables into a single 'virtual table', or apply specific sets of formulas(like avg cost or an item).… Read more

Conditional Split is a very powerful component, enabling you to send records in different directions or exclude them from the downstream altogether.  I use this component quite often in my SSIS packages.  In the industry I work in, publishing, there are many times we'll get a file in from…

Read more