Just over a year ago I posted a question on my favourite programming Q&A site, Stack Overflow regarding an issue I was having with processing some large, variable column data files using SSIS (SQL Server Integration Services). Thanks to the great community there, I received an answer rather quickly and was able to develop a solution to solve the problem. I’ve been intending to document this for some time, as it’s a good solution, combining SSIS Control and Data Flow logic with custom C# code and a flexible SQL server database schema. I was working with a database schema which was set up to handle variable columns from a number of different files by transposing the columns into rows within the database. The upside of this is that it could support any number of columns from different files, in any order and with any type of data. The downside is that each row in a file, suddenly becomes row x column number of rows in the database, meaning processing large files can slow down exponentially if not handled correctly.

July 12th was a good day for all SQL Server enthusiasts and professionals as Microsoft announced the release of both Service Pack 1 for SQL Server 2008 R2 as well as Community Technology Preview 3 for the next installment in the series, Codename Denali.

SP1 brings with it a number of stability and security fixes as well as some more advanced configuration options to fine-tune performance.  The most interesting update from my point of view is the updated syntax on the FORCESEEK index hint, which now has extra options to further fine tune queries.