Recent Posts

SQL Server code to pivot Key Value Pairs into fixed Colums

Square peg and a round hole.  Metaphor for a misfit or nonconformist.

Today’s requirement was to translate 550k rows of product data from a long list of name / value pairs into a wide table with up to 15 name value pairs per product.           I tried a few techniques to achieve this, and found SQL Server’s windowing functions to do a pretty good job of building a cross-tab from this flat list.

read more

Seeding an integer sort code into a Data Warehouse from a varchar

Seeding an integer sort code into a Data Warehouse from a varchar

I found this a bit of a challenge this morning, and as it’s my first use of windowing functions in SQL Server I thought I’d share. Our requirement was to add a sort code to our dimension tables for various levels. For some reason many of our business keys are varchars, however I really wanted to get sort codes as integers. Here’s the code: This generates three distinct sort keys, one for each level. Quite nifty I reckon.                       Long term we’ll look to move this stuff into a source system somewhere, but for now, she’s sorted.

read more

Task Factory’s Upsert ‘Go Slow’ button

Task Factory’s Upsert ‘Go Slow’ button

If you’re using the Upsert component in Task Factory and notice upserts taking 10-20 minutes and would prefer them to take 10-20 seconds, try removing the tick from ‘Enable Identity Insert’.

read more

SSRS Missing Functionality

SSRS Missing Functionality

Just putting this out there cause surely I can’t be the only one missing this… If you install SSRS on SQL Server 2016 Standard Edition, quite a few of the menu items will be missing. Items such as: Mobile Reports KPI Reports Branding The solution.. install SSRS on Enterprise Edition.

read more

Script to split huge text file into chunks with headers

Script to split huge text file into chunks with headers

As a powershell newbie I’m pretty happy with this one, even if it relies on calling the split utility from cygwin and a dos copy command to perform… The split.exe is the windows port of the unix split command, I downloaded this as part of cygwin. However I didn’t want to install cygwin on the server so just copied the split.exe file across. This performs pretty well on our VM, it processed 200GB of text files into 64MB chunks in about 2 hours. It’s a one off process to seed the historical invoices into the DWH, so I have need to seek further enhancements.

read more

Capturing load errors with SSIS / SQL2016

Capturing load errors with SSIS / SQL2016

Using SQL 2016 and SSDT for Visual Studio 2015 I’ve been looking for a few weeks how to get an error in loading a .csv file to show the offending column, and the name of the error. There were some improvements to make this possible via a script object in SQL 2016,  however they’ve moved a few of the functions around which made the code posted online, including on the MS site, outdated. Here’s the C# code that is working with the latest releases:

read more

SSIS – Checking if a file exists

SSIS – Checking if a file exists

I’ve found that SSIS will throw an exception if the CSV file I want to load is missing. While I do have a script that checks all my connections, this doesn’t seem to do enough for text file data sources and lets them pass. The solution I’ve found is using Powershell to test for the file and return the result to SSIS.

read more

Another SSIS Framework – Beginner’s Guide

A dummies book. Yep, I'm really scraping the bottom of the barrel for puns now.

Another SSIS Framework (for n00bs) While there is some good high level documentation written by SqlArchitect, I hope for this guide to compliment his efforts by providing some step by step step instructions for those new to SSIS against a clean SQL 2016 environment using SSISDB.

read more

SSIS Framework Selection

SSIS Framework Selection

After 16 years of BI development in the IBM Cognos stack, I’ve recently jumped ship over to Microsoft and have faced a pretty steep learning curve as I plan to build a robust data warehouse and reporting solution in three months! To jump start this activity I’ve been reviewing SSIS Frameworks, which will provide the ‘belts and braces’ around ETL code without my having to invest months building this logic around logging, notification, error detection / recovery and job flow from scratch.

read more

Bursting Gotcha

After spending what seems like ages trying to troubleshoot why an account does not have access to burst a report, I thought I’d share the solution. In addition to setting up the capability for a user to burst content, this is also a capability at the package level. Duh.

read more