Parameters in Paginated reports allow for us to tailor report contents based on user selected criteria. These were inherited from Reporting Services as a precursor to the slicers and filters in Power BI reports. Coming from a Reporting Services background I have a soft spot for pre-defined paginated reports. However these parameters can sometimes get […]
The UNION and UNION ALL operators allow us to combine results, but there’s no guarantee that each set of results uses the same data types. So what data types are returned? For the longest time I thought the data types from the first set of results were used for the final results. That’s not the case. Understanding how this […]
When restoring a database in different environments we may stumble across orphan users. This typically manifests through failed connections to the database. Here we’ll have a quick look at what the issue is and 3 ways to fix it. What are orphaned users When users are created in a database they’re mapped to the login […]
Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined. We’ll use the sample table from the introduction to […]
Last time out we discussed blocking transformations, what they are, the impact of them, and touched on how to deal with them. In this post we’re going a step further to tackle one of them head on. Here we’ll demonstrate the impact of blocking caused by the Sort transformation, and look at two options for solving […]
We have a platter of transformations to use when constructing data flows in SSIS packages. Not all transformations are equal though, and some can catch us out with performance impact as data volumes scale. In this post we’ll look at blocking transformations which can trip us up if we’re not careful with them (or avoid […]
Package configuration allows us to retrieve variable values from a variety of sources. We recently looked at storing the values in a SQL database. We can elevate the database configuration a step further by using system level Environment Variables. In this post we’ll walk through that process. Using environment variables Where we previously looked at storing […]
When ingesting files in SSIS via Flat File Connections, a consistent format is key. Sometimes that isn’t the case. Here we’ll look at an example where the carriage return (CR, \r) may or may not be included in the file. The problem A file format can change as easy as tweaking the file in an editor […]
When constructing or investigating a SSIS package we can run into a variety of issues. To help resolve there are various techniques which can be used to troubleshoot the package. Whilst we have the Progress tab for the package to tell us what’s happened during execution, it’s usually more effective to debug packages in flight. […]
A few weeks ago I demonstrated the simplicity of performing point-in-time database restored in Azure Managed Instance. Whilst that has a lovely front-end, it can be just as easy with a proc call on your traditional SQL instance. Let’s see how to use two open source tools to achieve this simplicity. Back it up Before we […]
Andy Brownsword 