Tag Archive: ETL

  • 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
  • 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
  • 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
  • Pattern matching in SSIS using Regular Expressions and the Script component

    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
  • Forecasting the Performance of SSIS packages

    SQL Server Integration Services (SSIS) packages are used in numerous scenarios for moving data from A to B. Often they are developed and tested against a cutdown, often static, subset of data. One of the problems with this is that yes you’re testing the functionality of the package as it’s being developed, but there’s no way to determine whether the performance will scale up to a full size production environment. This level of testing is…

    » Read more