RichardSwinbank.net

com.microsoft.cdm for Synapse Spark v3.4

Sun 04 Apr 2025 16:15:00 +0000

The Spark connector for Synapse Link for Dataverse quietly disappeared in Synapse Spark runtime v3.4. This article looks at a possible workaround.

Deploy TMDL semantic models to Power BI

Fri 28 Mar 2025 18:00:00 +0000

Deploy TMDL semantic models to Power BI using Azure DevOps CI/CD pipelines!

Unreliable logging in data factory pipelines

Mon 03 Mar 2025 08:49:00 +0000

In this post I take a look at discrepancies between data factory activity execution details - in Fabric, Synapse or ADF - and what gets reported in activity logs.

Two-aggregate pivot

Tue 10 Sep 2024 21:54:00 +0000

In September 2024's T-SQL Tuesday, Deepthi Goguri wants to hear about a recent technical issue you resolved. I recently ran into an issue with a client where a Spark Structured Streaming query I was developing just refused to run, and I'll talk about that here – thanks for hosting, Deepthi!

DevOps for SQL databases

Tue 13 Aug 2024 19:55:00 +0000

In August 2024's T-SQL Tuesday, Mala Mahadevan asks us to reflect on how we manage database code. Thanks for hosting, Mala!

Create Power BI deployment pipelines automatically

Wed 17 Jul 2023 22:00:00 +0000

In this post, I accelerate creation of new reports by building their deployment pipelines automatically. Create your report, push it to Git, see it appear in Power BI!

Can you just...?

Wed 21 Jun 2023 15:30:00 +0000

Communicating technical complexity to non-technical colleagues can be tough -- but it's essential if you want to explain why you're going to need 6 months to build a report 😂. This post tries to help with a one-slide explainer.

Working with multiple Power BI dataset environments

Tue 06 Jun 2023 09:37:00 +0000

In a recent article I talked about deploying Power BI reports through any number of environments (not just three, and automatically!). In this new post I look at how to do exactly the same thing for standalone/shared PBI datasets – and how to bind thin reports to different source datasets automatically during deployment.

Reusable deployment pipelines for Power BI

Tue 23 May 2023 07:25:00 +0000

Azure DevOps pipeline templates allow you define common functionality once, then reuse it in many pipelines. A great use case for this is publishing #PowerBI reports automatically -- templated deployment makes it easy to create a pipeline per report, giving you low overhead deployment with fine-grained control.

Publish automatically to Power BI environments with Azure DevOps pipelines

Tue 09 May 2023 09:45:00 +0000

This post shows you how to use Azure DevOps pipelines to automate publishing of Power BI reports to different environment workspaces.

Better version control for Power BI datasets

Wed 26 Apr 2023 08:32:00 +0000

Version controlling a Power BI PBIX file along with all its data can be problematic, for reasons of both size and privacy. In this post I look at managing Power BI datasets separately, without data, and deploying them using Azure DevOps pipelines.

Pro DevEx for Power BI

Wed 12 Apr 2023 08:45:00 +0000

After deploying an obsolete version of a buggy #PowerBI report to the wrong workspace, I realised it wasn't the report I meant anyway 😒. Time to invite #DevOps to this party.

Metadata-driven SQL code generation

Tue 11 Oct 2022 07:40:00 +0000

October 2022's T-SQL Tuesday is hosted by Steve Jones – thanks Steve 😊. Steve's invitation this month is to write about using dynamic SQL – that is, T-SQL statements built up from text strings and then executed as code.

Argument {0} is null or empty. Parameter name: paraKey

Tue 12 Jul 2022 20:04:00 +0000

I encountered this error while using Azure Data Factory's Script activity -- with a bit of luck, writing it down will help me remember how to fix it next time!

SQL database project (SSDT) merge conflicts

Wed 09 Mar 2022 08:21:00 +0000

More merge conflict fun! This time, using pre-commit hooks to duck conflicts in SSDT .sqlproj files.

Merge conflicts in tabular models

Tue 01 Feb 2022 08:40:00 +0000

I sometimes find working with Visual Studio's projects a challenge in multi-developer environments, because each project type seems to have its own vulnerability to Git merge conflicts. This post looks at how to avoid them when working with tabular models for Power BI Premium, AAS or SSAS.

Ordered STRING_SPLIT

Tue 18 Jan 2022 08:34:00 +0000

I'm a bit late to this party, but some long-awaited news is finally here – STRING_SPLIT now returns the ordinal position of string elements in various Azure SQL offerings.

Breaking the rules

Tue 11 Jan 2022 23:34:00 +0000

January 2022's T-SQL Tuesday is hosted by Andy Yun -- thanks Andy! This month, Andy's asking about learning that changes your opinion.

Infrastructure as nearly-all-code

Tue 14 Sep 2021 22:17:00 +0000

T-SQL Tuesday #142 (September 2021) is hosted by Frank Geisler. Frank's choice of subject is "using descriptive techniques to build database environments".

More Get Metadata in ADF

Tue 23 Feb 2021 20:00:00 +0000

Last year I wrote a post about doing this in pure ADF, with really terrible performance. By way of apology, I've had another go in an Azure Function!

Azure Data Factory, the ADF UX and Git

Tue 10 Nov 2020 08:45:00 +0000

If you're using Azure Data Factory (ADF), you're probably using Git and almost certainly using the ADF User Experience (ADF UX) – ADF's online integrated development environment (IDE). These three components are so closely interlinked that sometimes it's hard to think about them separately – in this article I try to do exactly that.

Catch-22: Automating MSI access to an Azure SQL Database

Thu 28 Oct 2020 08:45:00 +0000

A problem I ran into recently is how to automate granting access to SQL databases in different environments. I'm using Terraform to build parallel data engineering environments which – amongst other things – include one or more SQL databases and instances of Azure Data Factory (ADF). ADF pipelines need access to databases, and I want to authenticate linked service connections using the factory's managed identity (MSI). Building this automatically is harder than it sounds!

The Ice Cream Van of Abstraction

Tue 13 Oct 2020 08:35:00 +0000

October 2020's T-SQL Tuesday is hosted by Rob Volk (b|t) with the subject Data Analogies, or: Explain Databases Like I’m Five! Thanks for hosting, Rob! It turns out that I don't do much specifically databasplaining™ by analogy, but there's one analogy I go back to again and again when talking to people unfamiliar with a useful concept, both inside and outside of SQL Server: abstraction.

Google Analytics API pagination in Azure Data Factory

Wed 07 Oct 2020 08:45:00 +0000

In a previous post I created a pipeline to retrieve data from the Google Analytics reporting API, using an OAuth 2.0 access token for authorisation. Azure Data Factory's Copy data activity handles various styles of paged API response, but it doesn't support the approach taken by the Google Analytics reporting API. In this post I look at how to make that work.

Get Metadata recursively in Azure Data Factory

Tue 29 Sep 2020 08:45:00 +0000

Azure Data Factory's Get Metadata activity returns metadata properties for a specified dataset, but not recursively. In this post I try to build an alternative using just ADF.

Extract data from Google Analytics with Azure Data Factory

Thu 10 Sep 2020 09:10:00 +0000

In a previous post I prepared an ADF pipeline to make authorised Google Analytics API requests, using an Azure Function to obtain an OAuth 2.0 access token. In this article I'm going to use the returned token to authorise an API connection and extract data from Google Analytics.

Automate to replicate

Tue 08 Sep 2020 13:55:00 +0000

In September 2020's T-SQL Tuesday, Elizabeth Noble (b|t) wants to know what members of the SQL community have automated to make their lives easier. Thanks for asking, Elizabeth 😀.

Access Google Analytics with Azure Data Factory

Fri 28 Aug 2020 08:45:00 +0000

At the time of writing, Azure Data Factory has no connector to enable data extraction from Google Analytics, but it seems to be a common requirement – here's how to do it using ADF's current feature set.

Parameterising the Execute Pipeline activity

Thu 13 Aug 2020 08:45:00 +0000

A shortcoming of Azure Data Factory's Execute Pipeline activity is that the pipeline to be triggered must be hard-coded into the activity – so it's impossible to use metadata-driven approaches like iterating over a list of pipeline names. This post looks at an alternative approach.

Time capsule

Tue 11 Aug 2020 11:00:00 +0000

T-SQL Tuesday for August 2020 is hosted by Tamera Clark (b|t). She's asking for help in assembling a #SQLCommunity time capsule -- thanks for hosting, Tamera!

Why automate ADF pipeline testing?

Fri 24 Jul 2020 10:00:00 +0000

Since writing my series on automated testing Azure Data Factory pipelines, I've had a few questions along the lines of "why bother?". One reader commented that "a typical ADF developer tests their pipeline doing debug runs". This is exactly how I develop pipelines: make a change, run the change, repeat until the pipeline does what I want. "Why bother with more testing?" is a good question!

Default fault

Tue 14 Jul 2020 08:30:00 +0000

July 2020's T-SQL Tuesday is hosted by Kerry Tyler (b|t). Kerry describes pilots' use of plane crash reports to learn aviation safety lessons, and asks for similar – albeit hopefully lass catastrophic – tales of SQL Server-related disaster.

Dropping temporary tables

Thu 09 Jul 2020 08:30:00 +0000

Local temporary tables – tables with names that begin with a single # character – are dropped automatically by SQL Server when they are no longer in scope. So why drop them explicitly at all? Here are some ideas!

Print big

Tue 30 Jun 2020 15:30:00 +0000

A feature of T-SQL is that strings longer than 8000 bytes are truncated by PRINT. If you haven't already discovered this, you might wonder why it's a problem – the answer (for me at least) is dynamic SQL.

From Azure Pipelines to GitHub Actions

Thu 25 Jun 2020 08:30:00 +0000

I use Azure Pipelines a lot, both for CI/CD (including automated testing for Azure Data Factory) and for building disposable Azure environments repeatably. Most of my code, however, lives in GitHub. GitHub provides its own service for automating CI/CD software workflows – GitHub Actions – and in this post I compare it to Azure Pipelines.

Calculating Azure Data Factory test coverage

Thu 17 Jun 2020 08:30:00 +0000

This is the sixth and final article in a series on automated testing for Azure Data Factory pipelines. In software engineering, code coverage (or test coverage) measures the proportion of a program's source code executed by a given test suite. In this article I use execution history data to measure the proportion of a data factory's activities (across all pipelines) executed during a full test run – the test suite's activity coverage.

The under-appreciated Start → Run

Tue 09 Jun 2020 13:00:00 +0000

This month's T-SQL Tuesday, hosted by Kenneth Fisher (b|t), asks for specifically non-SQL related tips and tricks. I found that much harder than any number of technical problems, which I guess is the point of T-SQL Tuesday...

Unit testing Azure Data Factory pipelines

Wed 03 Jun 2020 11:00:00 +0000

In part three of this series I looked at functional tests for ADF pipelines: verifying, in isolation, that pipelines are “doing things right”. In this post I'll be testing isolated pipelines to check that they're “doing the right things” – this is one description of a unit test.

Testing Azure Data Factory in your CI/CD pipeline

Thu 21 May 2020 08:30:00 +0000

In my previous post I used ADF pipeline parameters to implement dependency injection for ADF pipelines and build isolated functional tests using the NUnit testing framework. In this article, I integrate the NUnit testing solution into an Azure DevOps pipeline, so that I can run tests automatically whenever changes are made to ADF resources.

A tale of two smartphone apps

Tue, 12 May 2020 10:30:00 +0000

This month's T-SQL Tuesday is hosted by Glenn Berry (b|t). Glenn's invitation was “to write about what you have been doing as a response to COVID-19”, and it feels like my answer should be this: not much...

Isolated functional tests for Azure Data Factory

Wed, 06 May 2020 08:30:00 +0000

In this article, I'll look at isolating a pipeline from its external dependencies in order to test it independently and with a range of testing scenarios. I'll be trying to establish that a pipeline is “doing things right” in isolation – this is a functional test.

Automate integration tests in Azure Data Factory

Sun, 26 Apr 2020 08:30:00 +0000

In my previous post, I set up and ran one basic test of a single pipeline. In this article, I refactor my VS testing solution to make it easier to add new tests and to test new pipelines.

Set up automated testing for Azure Data Factory

Mon, 20 Apr 2020 08:30:00 +0000

Test automation allows you to run more tests, in less time, with guaranteed repeatability. If you change an existing ADF dataset definition for a new ADF pipeline, how do you know haven't broken something else? Automatically re-testing all your ADF pipelines before deployment gives you some protection against regression faults. Automated testing is a key component of CI/CD software development approaches: inclusion of automated tests in CI/CD deployment pipelines for Azure Data Factory can significantly improve quality.

Errors in script tasks and components

Sun, 26 Jan 2020 08:30:00 +0000

Unhandled errors thrown out of SSIS script tasks are usually accompanied by the message “Exception has been thrown by the target of an invocation” – this isn't very informative!

A more manageable approach is to wrap as much of your code as possible inside a try/catch, then raise caught errors to SSIS for cleaner failure and easier diagnosis. This article shows you how to do that.

Managing extended properties

Thu, 02 Jan 2020 08:30:00 +0000

Extended properties are a means of attaching user-defined key-value pairs to database objects. This page provides some utility scripts to facilitate interaction with extended properties.