Another week, another error message to contend with! Typically, there is no way of predicting how a full diagnosis and resolution of a troublesome fault will take. Often, if you don’t follow the mantra of keeping things sample, you might end up spending many fruitless hours resolving an ultimately simple issue. In other cases, you may need to rely on inferred knowledge from other areas to assist you on your way.
This last scenario was undoubtedly the case earlier this week when I was working with Azure Data Factory V2. I was attempting to generate a Resource Manager template for my completed data factory project. Once created, this can then be used as part of an Azure DevOps Pipeline to deploy the data factory out into different environments. The project in question was a series of data movement tasks between two Azure SQL Server databases. The process behind generating Resource Manager templates is usually pretty straightforward:
- First of all, make sure you have associated an Azure DevOps/GitHub repository to your data factory.
- After authoring your data factory and moving into your master branch, select the Publish All button. This action will simultaneously deploy out all of your data factory resources to the data factory itself and also generate the Azure deployment scripts into a separate branch, called adf_publish.
As you can see, a pretty straightforward process, which I like! Unfortunately, the other day, this was not quite working as expected for me, and I was getting the following message instead:
Message: Unable to process template language expressions for resource ‘/subscriptions/229c164c-0f4e-4a2d-90bc-8264758a3e0a/resourceGroups/my-rg/providers/Microsoft.DataFactory/factories/my-adf/datasets/MyTable’ at line ‘1’ and column ‘37433’. ‘Unable to parse language expression ‘dbo].[MyTable’: expected token ‘LeftParenthesis’ and actual ‘RightSquareBracket’.’
Rather annoying! Now, as mentioned earlier, sometimes you can guess a resolution to a problem by bringing knowledge in from other areas. In this case, because the error mentions square brackets, I knew that square brackets were reserved characters for arrays within JSON - the underlying language for an Azure Resource Manager template. This restriction, therefore, could cause problems if a square bracket is misused. Upon closer inspection of my factory’s data sources, I noticed that they contained square brackets for each object name, e.g. [dbo].[MyTable]. Now, when working with Transact-SQL, the use of square brackets is widespread and, in most cases, recommended; particularly if the name of your objects are using reserved keywords. A good example of this is the word ORDER, which may be useful in describing a table of customer orders — all fine, except that this is also a reserved keyword for sorting data. Therefore, the use of square brackets in this scenario is an absolute requirement to ensure your table is referenced correctly as part of any query.
So, bearing all of this in mind, the solution is relatively straightforward - remove the square brackets from each data source and, optionally, any reference to the underlying table schema. For this example, therefore, [dbo].[MyTable] would need to be changed to just MyTable. Upon fixing this, the Publish All button should start working as expected.
Without a shadow of a doubt, I was fortunate with this particular error. Without being familiar with the underlying mechanics of JSON, I imagine this could have descended into an agonising period of frustration and headbanging to resolve. Fortunately though, thanks to this and the rather obvious clue that the error message provides, the problem was relatively quick to resolve. This meant I could move on quickly to getting my stuff deployed out, instead of languishing in a repo and being of no use to anyone. 🙂