Cannot execute U-SQL script from ADF: ScriptPath should not be null

Question

We are trying to run an U-SQL script from a ADFv2, but there is an error preventing the execution. Considering that MS documentation regarding this issue is not helpful, I am again asking in SO for some help fixing this issue.

1. The initial problem is how to deploy the U-SQL script to the ADLA. We could not find anything helpful and ended up just copy-paste the script on ADLA and on an Azure Blob Storage, in 2 formats: .usql and .txt. (this can also be one of the problems).

2. We created the ARM according to MS documentation, but it is failing with an error: ScriptPath should not be null which is odd since this value is already specified even on the Linked Service and also on the activity.

Below are the LS and activity we have created:

LinkedService:

{
    "type": "linkedservices",
    "name": "LinkedServiceofAzureBlobStorageforscriptPath",
    "dependsOn": ["[parameters('dataFactoryName')]"],
    "apiVersion": "[variables('apiVersion')]",
    "properties": {
        "type": "AzureStorage",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "DefaultEndpointsProtocol=https;AccountName=<account>;AccountKey=<key>;EndpointSuffix=core.windows.net"
            }
            "scriptPath": "container\\script.txt"
            //"scriptPath": "https://storage.blob.core.windows.net/container/script.txt"//"wasb://container@storage/script.txt",
        }
    }
}

Activity:

{
            "type": "DataLakeAnalyticsU-SQL",
            "typeProperties": {
                //"script": "master.dbo.sp_test()",
                "scriptPath": "container\\script.txt"//"scriptPath": "https://storage.blob.core.windows.net/container/script.txt"//"wasb://container@storage/script.txt",
                "scriptLinkedService": {
                    "referenceName": "LinkedServiceofAzureBlobStorageforscriptPath",
                    "type": "LinkedServiceReference"
                },
                "degreeOfParallelism": 3,
                "priority": 100
            },
            "name": "CopyFromAzureBlobToAzureSQL",
            "description": "Copy data frm Azure blob to Azure SQL",
            "linkedServiceName": {
                "referenceName": "AzureDataLakeAnalyticsLinkedService",
                "type": "LinkedServiceReference"
            }
        }

Also tried this approach but still with no success.

This is a dummy script that we are testing:

@a =
    SELECT *
    FROM(
        VALUES
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        ),
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        ),
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        ),
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        ),
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        ),
        (
            "Contoso",
            1500.0
        ),
        (
            "Woodgrove",
            2700.0
        )
        ) AS 
              D( customer, amount );
OUTPUT @a
TO "/data"+DateTime.Now.ToString("yyyyMMddHHmmss")+".csv"
USING Outputters.Csv();

but it would be great if you could point to some more complex example with some code behind in the script.

Thank you!

Update 26.01.2018

After consulting with MS regarding the deployment of usql, we came with a combination of powershell commands:

• we execute a script that uploads the .dll that are located in the bin of the u-sql assembly on a folder on the Datalake;
• then loop through the directory and create each of the assemblies separately;
• for the u-sql scripts we have created them as stored procedures on the Datalake Analytics and upload a simple u-sql script that executes those procedures with the needed parameters;

Answer 1

You don't need the script path in the linked service.

The blob linked service should just be:

{
    "name": "Blob Name",
    "properties": {
        "type": "AzureStorage",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "DefaultEndpointsProtocol=https;AccountName=etc"
            }
        },
        "connectVia": {
            "referenceName": "Your IR Ref",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Then in the activity reference the script using the container and file name like the below with the reference name for the linked service.

    "activities": [
        {
            "name": "U-SQL1",
            "type": "DataLakeAnalyticsU-SQL",
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 20
            },
            "typeProperties": {
                "scriptPath": "u-sql1/Test",
                "degreeOfParallelism": {
                    "value": "5",
                    "type": "Expression"
                },
                "priority": 1,
                "compilationMode": "Full",
                "scriptLinkedService": {
                    "referenceName": "Your Blob Ref",
                    "type": "LinkedServiceReference"
                }
            },
            "linkedServiceName": {
                "referenceName": "Your ADLa Ref",
                "type": "LinkedServiceReference"
            }
        },

For info, I've ignored the MS documentation and created this JSON using the new dev UI as I have private preview access. The above has been tested and works as I used it in a blob post here:

https://mrpaulandrew.com/2017/12/20/controlling-u-sql-job-aus-with-azure-data-factory-v2-pipeline-parameters/

Hope this helps.