Tag Archive: SSIS

  • How to resolve SSIS “Row yielded no match during lookup” error.

    Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you! A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results. So why was SSIS…

    » Read more
  • Automate changing SSIS connection at runtime

    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
  • System OutOfMemoryException when deploying SSIS .ispac

    If you’ve tried deploying a .ispac using the Integration Services Deployment Wizard, you may have come across the following error: By default SQL Server uses the 32-bit version of the Deployment Wizard, which is why you’ll be getting the Out of Memory error. If you instead launch the Wizard from the 64-bit location, (default is C:\Program Files\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe), then you should be able to…

    » Read more
  • Azure SSIS – How to Setup, Deploy, Execute & Schedule Packages

    Welcome back to work in 2018! 🙂 Let’s get stuck in with a hot topic. How do we actually use our beloved SQL Server Integration Services (SSIS) packages in Azure with all this new platform as a service (PaaS) stuff? Well, in this post I’m going to go through it end to end. Post Contents Azure Services Setup Azure Data Factory v2 Azure SQL Instance Azure SSIS IR Creating & Deploying the SSIS Package SSIS Azure Feature Pack…

    » Read more
  • LogParser Performance Inserting into 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

    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

    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

    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)

    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
  • Using T-SQL Merge to load Data Warehouse dimensions

    In my last blog post I showed the basic concepts of using the T-SQL Merge statement, available in SQL Server 2008 onwards. In this post we’ll take it a step further and show how we can use it for loading data warehouse dimensions, and managing the SCD (slowly changing dimension) process. Before we start, let’s have a quick catch up on what an SCD is… What is a Slowly Changing Dimension (SCD)? If you want a full explanation of…

    » Read more