I have a U-SQL DB Project (USQLdb) that defines a U-SQL database and it's constituent tables, procedues, etc. This project also references two assemblies for use in one of the stored procedures. The DLL files are held within a folder called assemblies within the U-SQL Data Root folder and are referenced within the database using the following script:
CREATE ASSEMBLY IF NOT EXISTS [Microsoft.Analytics.Samples.Formats]
FROM "/assemblies/Microsoft.Analytics.Samples.Formats.0.0.0.0/Microsoft.Analytics.Samples.Formats.dll";
This works when deploying to Local-machine or to Azure.
For testing purposes, I have added a U-SQL Application Project (USQLScripts) that references USQLdb, with U-SQL scripts that execute a stored procedure each with the aim of setting up Unit Testing.
When trying to run these scripts against Local-project: USQLdb however, database deployment fails. From the logs it is because the USQLdb deployment script cannot find the referenced assemblies in the Local-project data root folder:
*** Error : (204,6) 'Assembly file 'C:\<Solution Folder>\USQLScripts\bin\Debug\DataRoot\assemblies/Microsoft.Analytics.Samples.Formats.0.0.0.0/Microsoft.Analytics.Samples.Formats.dll' could not be read.'
I have specified the USQLScripts Test Data Source as the local U-SQL Data Root folder which copies all files found to the Local-project working directory here:
C:\<Solution Folder>\USQLScripts\bin\Debug\USQLScripts_altdata_5qktnwfj.gln\data'
though per the error message above, the USQLdb Assembly Reference is trying to find them here:
C:\<Solution Folder>\USQLScripts\bin\Debug\DataRoot
As the DataRoot folder is completely cleaned and recreated on each Local-project execution, how can I either get the assemblies into the DataRoot folder on execution or reference them approriately without changing the address in the U-SQL script included earlier, which works as required when deployed to Azure?
Turns out, that if I remove the U-SQL Database reference in USQLScripts, the files that are in the Test Data Source folder are now copied to the C:\<Solution Folder>\USQLScripts\bin\Debug\DataRoot folder, but the scripts cannot execute as the database they are trying to execute against hasn't been referenced. I get the impression that I am either missing something or have hit a bug/unintended behaviour...
Turns out I was missing something.
What I thought was an error in referencing the assemblies was actually an error in building the USQLdb project, that resulted in the assemblies not being available and a red herring of an error being thrown.
The culprit was the Target Framework Version of the assemblies being higher than both the USQLdb and USQLScripts projects. It appears this can only be changed by editing the .usqldbproj files manually in a text editor (specifically the <TargetFrameworkVersion> value), as there is no option within the Visual Studio UI to edit this project property that I can find.
Once I upped the Target Framework Version in the U-SQL projects to match those of the assemblies, everything now builds, deploys and executes as expected.