Came across this issue when trying to get a range of values that are calculated dynamically at runtime. Using the script activity in ADF, I wanted a result set that I could reference as parameters for a notebook later in the pipeline but was experiencing an error I had not seen before referencing null arguments.
The Problem
The script is simple – get a few key dates from the last few months and set these as variables. Finally, return these dates using a SELECT query, ultimately forming the result set that I wanted. (Script Below)
DECLARE @interval_one_start DATE
DECLARE @interval_one_end DATE
DECLARE @interval_two_start DATE
DECLARE @interval_two_end DATE
— get today as interval two end date
SELECT @interval_two_end = [date] FROM dim.[date] WHERE [date] = FORMAT(getdate(), ‘yyyyMMdd’)
SET @interval_two_start = DATEADD(month,-2, DATEADD(month, DATEDIFF(month, 0, @interval_two_end), 0))
SET @interval_one_start = DATEADD(month,-1, DATEADD(month, DATEDIFF(month, 0, @interval_two_start), 0))
SET @interval_one_end = EOMONTH(DATEADD(month,2, @interval_one_start),0)
SELECT @interval_one_start
UNION ALL
SELECT @interval_one_end
UNION ALL
SELECT @interval_two_start
UNION ALL
SELECT @interval_two_end
Upon executing this activity, I got the following error:
“Argument {0} is null or empty.\r\nParameter name: paraKey”
As a result, my first thought was to check my script activity and look for a parameter, in particular one called paraKey. My script activity is configured as follows – no parameters, nothing interesting or different:
The Solution
The underlying issue was no alias for my columns in the final SELECT statement. However, following activities that reference the output of this script activity need a reference point. They need to know what field that specific values belong to, otherwise how could you point to it? You also may want to return the JSON object rather than just a value – you and Synapse need the correct context for what that value represents.
So I simply added the following:
SELECT @interval_one_start as [Date]
UNION ALL
SELECT @interval_one_end
UNION ALL
SELECT @interval_two_start
UNION ALL
SELECT @interval_two_end
You may be applying functions, logic or calculations to a column in your intended result set and therefore you need to alias these columns appropriately. When you consider that the result set is returned as JSON which can be referenced later in the pipeline, it makes sense that an identifier is required for that value in the JSON array.
