Call today 0845 643 6463

Blog

  • SQL LAST_VALUE() wrong results

    March 2nd, 2020 in Blog, SQL Server

    You may have come across the following scenario when using the LAST_VALUE() function. You want to find the first or last value loaded in a result set, therefore you use the FIRST_VALUE() and LAST_VALUE() functions like below: You expect to get “A” for every record in the FirstValue column and “E” for every record in the LastValue column.However you see the following output: LAST_VALUE() on its own is implemented from the…

    » Read more

  • System OutOfMemoryException when deploying SSIS .ispac

    February 21st, 2020 in Blog, SSIS

    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

  • Creating Power BI Layouts using PowerPoint

    December 2nd, 2019 in Blog, Power BI

    First question, why bother with layouts?Using layouts in Power BI allows a user to make their visuals stand out better, the page looks professional and more appealing to its audience. Second question, why PowerPoint?The default page size in Power BI desktop is 16:9, (this trick doesn’t work for other Power BI page sizes), which is identical to a PowerPoint slide.Therefore whatever is designed in PowerPoint will fit onto a Power BI page…

    » Read more

  • Capturing Insert and Update Counts from Merge

    November 9th, 2019 in Blog, Uncategorized

    This post shows hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement. This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse. You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns. The basic syntax is: INSERT INTO XXXX SELECT [Action] FROM ( MERGE XXXX AS Target USING XXXX…

    » Read more

  • Power BI Sentinel

    March 25th, 2019 in Blog, Uncategorized

    We all know and love Power BI, because of it’s power and ease of use, there’s a good reason why it’s number 1 in the Gartner Quadrant. But how to we avoid ‘Power BI Hell’, the new version of our old nemesis ‘Excel Hell’? How do we keep track of which data sources are being used by which datasets and reports? How to we perform an impact assessment of updating/changing a dataset? What happens if someone…

    » Read more

  • SSAS Tabular Deployment Wizard fails Newtonsoft.Json error

    July 25th, 2018 in Analysis Services, Azure, Blog

    The Scenario Deploying an Analysis Services Tabular model to SSAS Azure using the Analysis Services Deployment Wizard. Both Visual Studio 2017 & SQL Server 2017 installed on the client. Try and click on the ellipses to change the data source connection string or impersonation information results in a Newtonsoft.json error: “Could not load file or assembly ‘Newtonsoft.Json, Version 6.0.0.0, Culture=neutral, ……”…

    » Read more

  • Lookups and If Conditions in Azure Data Factory v2 (ADFv2)

    June 5th, 2018 in Azure, Blog, Data Factory, SSIS

    Azure Data Factory v2 (ADFv2) has some significant improvements over v1, and we now consider ADF as a viable platform for most of our cloud based projects. But things aren’t always as straightforward as they could be. I’m sure this will improve over time, but don’t let that stop you from getting started now. This post provides a walk through of using the ‘Lookup’ and ‘If Condition’ activities to do some…

    » Read more

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

    January 8th, 2018 in ADFv2, Azure, Blog, PowerShell

    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

  • Business Intelligence in Azure – SQLBits 2018 Precon

    November 17th, 2017 in Azure, Blog, Community

    What can you expect from my SQLBits pre conference training day in February 2018 at the London Olympia? Well my friends, in short, we are going to take whirlwind tour of the entire business intelligence stack of services in Azure. No stone will be left unturned. No service will be left without scalability. We’ll cover them all and we certainly aren’t going to check with the Azure bill payer before turning up the compute on our data…

    » Read more

  • RDP to Azure Batch Service Compute Nodes

    November 6th, 2017 in Azure, Blog

    Did you know it’s now possible to RDP to your Azure Batch Service compute nodes? I’ve used the batch service to handle the compute for my Azure Data Factory custom activities for a while now. Which I’ve basically been doing blindly because the code execution and logging is provided to ADF, with no visibility to the underlying pool of VM’s doing the work. Well, no more is this the case! In the Azure portal go to your Batch…

    » Read more