passing dynamic parameter to USQL script using data factory v2 USQL activity

Question

USQL

I have multiple stored procedures defined in USQL database with the following signatures.

MyDatabase.dbo.DoThing01(@ticketNumber int)
MyDatabase.dbo.DoThing02(@ticketNumber int)
MyDatabase.dbo.DoThing03(@ticketNumber int)

These stored procedures work when I call them from the azure analytics job UI in the portal manually.

I have data factory v2 pipeline setup, with an USQL activity that calls a script file located on azure datalake store which tries to pass a parameter value as @ticketNumber

What I have tried

Datafactory Activity

                      "typeProperties": {
                            "scriptPath": "Script.txt",
                            "degreeOfParallelism": 10,
                            "scriptLinkedService": {
                                "referenceName": "CampuslabsDatalakeStore",
                                "type": "LinkedServiceReference"
                            },
                            "parameters": {
                                "ticketNumber": "@item().InstitutionId"
                            }
                        }

Scenario 1 : USQL Script

DECLARE @ticketNumber int = -1;

MyDatabase.dbo.DoThing01(@ticketNumber);
MyDatabase.dbo.DoThing02(@ticketNumber);
MyDatabase.dbo.DoThing03(@ticketNumber);

ERROR : the script gets executed with default value of -1.

Scenario 2 : USQL Script

DECLARE @ticketNumber int;

MyDatabase.dbo.DoThing01(@ticketNumber);
MyDatabase.dbo.DoThing02(@ticketNumber);
MyDatabase.dbo.DoThing03(@ticketNumber);

ERROR : I get a compilation error that declaration of variable is wrong.

Scenario 3 : USQL Script

MyDatabase.dbo.DoThing01(@ticketNumber);
MyDatabase.dbo.DoThing02(@ticketNumber);
MyDatabase.dbo.DoThing03(@ticketNumber);

ERROR : I get a compilation error that variable is not defined.

My question is : How do you pass a dynamic value parameter to the USQL script from a v2 USQL activity

mabasile_MSFT mabasile_MSFT · Accepted Answer · 2018-03-15T23:37:23

When passing in parameters from ADF to a U-SQL script, you should declare a default value for the parameter in your script using DECLARE EXTERNAL. ADF v2 passes in parameters to U-SQL by writing in DECLARE statements at the very top of your script. However, multiple DECLARE statements for the same variable cause a compilation error - but having a DECLARE EXTERNAL and DECLARE statement won't cause an error, because DECLARE EXTERNAL's use case is to be overwritten by the DECLARE statement above it (for this very use case).

U-SQL requires that a variable has a value when it is defined, which is your Scenario 2 error.

Because Scenario 1 runs with the value declared in the script and Scenario 3 doesn't compile means your variables aren't getting passed in at all, even though your JSON script looks correct. My hunch is the error is in your dynamic parameter definition. Where are you passing the InstitutionId from? If it's a parameter defined for the whole pipeline, the value should be "@Pipeline().parameters.InstitutionId". It would be useful to see your whole ADF pipeline if possible (or at least where you're getting the item().InstituionId from).