Call today 0845 643 6463

Integration Services

  • Automate changing SSIS connection at runtime

    October 15th, 2021 in Blog, Integration Services, SQL Server, SSIS, Uncategorized

    Recently a customer came to us with an issue: “We have a primary/secondary AlwaysOn failover cluster, we want the data warehouse ETL to always pull from the secondary read-only replica. When the primary fails over, how can we detect this and repoint the ETL to the new secondary?” This led us to the following qualification steps:1. Is the secondary server accessible? a. Yes – Use it. b. No – Try the primary server.2. Is…

    » Read more

  • My experience in obtaining an MCSE

    August 7th, 2020 in Analysis Services, Blog, Business Intelligence, Integration Services, SQL Server

    Background I have recently completed my MCSE in Data Management and Analytics, and I wanted to share my experience of working towards and passing the exams that have led to me getting this MCSE. In this post I will cover some of the challenges I faced and provide some tips and advice, hopefully others following the same certification path, or other paths, will find this useful. I am a business intelligence developer at Purple Frog, I have…

    » Read more

  • BIML – What is it?

    February 17th, 2015 in .Net, Blog, Business Intelligence, Integration Services

    I’ve noticed a growing trend over the last year – the ever growing presence of BIML (Business Intelligence Markup Language). So what is it? What does it do? And do you need to learn it? What is BIML? Simply, it’s a way of defining the functionality of an SSIS (Integration Services) package. If you’ve ever opened an SSIS .dtsx file in notepad you’ll see a daunting mess of GUIDs that you really don’t want to…

    » Read more

  • LogParser Performance Inserting into SQL Server

    August 2nd, 2013 in Blog, Integration Services, SQL Server

    Recently I’ve been using LogParser a lot to import log files, such as W3C logs from an IIS web server. The distasteful format of these files is made palatable by the awesomeness that is LogParser; it just takes care fo it for you. (Check out this SQLBits session on LogParser by Jonathan Allen (Twitter | Blog) for a good intro to LogParser) However I’ve been suffering with very poor performance with large files, so started to…

    » Read more

  • SSIS Expression Editor and Tester

    December 6th, 2012 in Blog, Integration Services

    I stumbled upon a gem of a tool this morning, written by Darren Green (blog | twitter) of SQLBits & Bids Helper fame; the SSIS Expression Editor and Tester. Kudos to Darren, this is a great little tool for fine tuning and testing those complex SSIS expressions without having to keep running tests within SSIS. It’s downloadable with 3 versions, for SSIS 2005, 2008 & 2012, and doesn’t even need installing, it just runs as an…

    » Read more

  • MSc Dissertation – Performance of Loading SCDs in SSIS

    July 20th, 2012 in Blog, Business Intelligence, Integration Services, SQL Server

    Well after 3.5 years, I’ve finally completed my MSc Business Intelligence – hoorah! And to reward the time, effort and increased grey hair, they saw fit to give me a merit as well. During the last year I’ve been writing a thesis investigating the performance characteristics of loading data into data warehouse dimensions. Specifically loading Type 2 SCDs using SSIS. For those who have followed the previous posts and my…

    » Read more

  • SQLBits X Video available

    June 19th, 2012 in Blog, Business Intelligence, Community, Integration Services, SQL Server

    SQLBits X Video Now available The video of my talk at SQLBits X is now available on the SQLBits website here. The talk was focused on presenting the results of my MSc Business Intelligence dissertation, comparing the performance of different methods of using SSIS to load data warehouse dimensions, specifically type 2 SCDs. The talk also covers a comparison of the performance between traditional hard disks and solid state storage systems such as…

    » Read more

  • Automating T-SQL Merge to load Dimensions (SCD)

    April 6th, 2012 in Blog, Business Intelligence, Integration Services, SQL Server

    This is the 3rd post in the Frog-Blog series on the awesomeness of T-SQL Merge. Post 1: Introduction to T-SQL merge basics Post 2: Using T-SQL merge to load data warehouse dimensions In this post we’ll be looking at how we can automate the creation of the merge statement to reduce development time and improve reliability and flexibility of the ETL process. I discussed this in the 2nd half of a talk I gave at the UK technical launch of SQL…

    » Read more

  • Pattern matching in SSIS using Regular Expressions and the Script component

    July 8th, 2011 in .Net, Blog, Integration Services

    One of my favourite features of SSIS is the script component, and I know I’m not alone. Why? Because it brings the entire might of the .NET framework to SSIS, providing C# (in SQL 2008 onwards) and VB.NET extensibility for all those times where SSIS doesn’t quite have enough functionality out of the box. Such as? Well a problem I’ve come across a number of times is string parsing. Trying to search for and extract a specific pattern of…

    » Read more

  • Speed up SSIS by using a slower query

    February 17th, 2011 in Blog, Integration Services, SQL Server

    This isn’t a technical blog post of my own, but a shout out to Rob Farley and an excellent blog post explaining how to use SQL’s OPTION (FAST x) hint. He explains how you can speed up an SSIS data flow by slowing down the source query. It may seem illogical at first, but you’ll understand after you go and read Rob’s post! Read Rob’s post here: Speeding up SSIS using OPTION (FAST)

    » Read more