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

I encountered this error recently while using Azure Data Factory's (ADF) new(ish) Script activity to run a SQL query. It took me a while to find a fix, and when it happened again two weeks later I'd completely forgotten it. With a bit of luck, writing it down will help me remember next time – and if it helps you too, great 😊.

The Script activity is a great addition to the ADF toolbox. Prior to its implementation your options for running SQL scripts were:

• the Lookup activity, requiring your script to return a result set
• the Stored procedure activity, meaning that your script has to be implemented as a stored procedure.

The Script activity supports scripts that return result sets (“Query”) and those that don't (“NonQuery”). It also permits the use of parameters, allowing you to gain access to SQL query return codes and output parameters (not supported by the Stored procedure activity).

The Script activity is configured directly with a linked service, just like the Stored procedure activity – no dataset, because you might not be returning any data. Whatever I'm intending to use it for, I often start by writing a simple SELECT statement, just to check that the connection is working:

Nice and simple! But when I run it, here's that error 😢. And from this error message I can't easily tell what's wrong.

The problem here is that I haven't aliased my SELECTed value – instead of saying SELECT somevalue AS column_alias, I've just said SELECT somevalue. The fix is to ensure that all columns returned by the query are named: