Find Log Analytics Key of Azure Log Analytics Instance

 Issue: Find the keys to connect to a log analytics instance in Azure.

Solution:  You can find this under Log Analytics Workspace > Agents > Log Analytics Agent Instructions

Synapse Analytics ADF Error - Failed to encrypt sub-resource payload or linked service credentials [Solved]

Issue: While trying to publish an HTTP or REST linked service connection from master to live, we get an error if it is going through a self hosted Integration run time to an on-premise network. 

Publishing error

Failed to encrypt sub-resource payload

and error is: Failed to encrypted linked service credentials on self-hosted IR 'selfHostedIR', reason is: NotFound, error message is: No online instance..

Cause: When you have a HTTP call that happens inside an on-premise network and you use a self hosted integration run time to connect to this, the first time the linked service is published it has to validate the connection and hence requires the Self Hosted IR to be up and running. 

Solution: The solution is to have the self hosted IR( Azure VM in our case ) up and running while trying to publish from master to live. This enables the validation of the linked service connection, this fixed it for us.

Please do let us know if this fix worked for you. 

Storage Event Trigger Error - Register Azure Event Grid resource provider

Issue: Trying to create a storage event trigger fails in Synapse/ Azure Data Factory with the below error 

Register Azure Event Grid resource provider to your subscription before creating an event trigger.

Cause: This is caused because Azure Event Grid resource provider is not added or enabled under your Azure Subscription. 

If you are using Azure Synapse Analytics, then you also need to have the Microsoft.DataFactory resource provider regsitered.

Resolution: You can follow the steps to register event grid and data factory resource provider to your subscriptions.

Step 1: go to portal.azure.com and select the subscription where you want to create the storage event trigger.

Step 2: under the subscription go to settings > resource providers.

Step 3: Search and Register the two resource Providers 

Registering Microsoft.DataFactory resource provider

Registering Microsoft.EventGrid resource provider

Try again and now it should be working. Let us know if this worked for you.

ADF / Synapse - Error converting Excel to Parquet

Error in Synapse / Azure Data Factory

Aim - Build a pipeline that can take an excel as input in a file contianer and write into a parquet file. 

Issue:- When processing the file, the copy activity runs into an error saying one of the fields cannot have more than one value. 

"errorCode": "2200",

    "message": "ErrorCode=ParquetJavaInvocationException,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=An error occurred when invoking java, message: java.lang.IllegalStateException:field 42 (COUNTRY_NAME) can not have more than one value: [Ägypten]\ntotal entry:6\r\norg.apache.parquet.example.data.simple.SimpleGroup.add(SimpleGroup.java:106)\r\norg.apache.parquet.example.data.simple.SimpleGroup.add(SimpleGroup.java:196)\r\norg.apache.parquet.example.data.Group.add(Group.java:59)\r\norg.apache.parquet.example.data.Group.append(Group.java:137)\r\ncom.microsoft.datatransfer.bridge.parquet.ParquetBatchWriter.readRow(ParquetBatchWriter.java:112)\r\ncom.microsoft.datatransfer.bridge.parquet.ParquetBatchWriter.addRows(ParquetBatchWriter.java:60)\r\n.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,''Type=Microsoft.DataTransfer.Richfile.JniExt.JavaBridgeException,Message=,Source=Microsoft.DataTransfer.Richfile.HiveOrcBridge,'",

    "failureType": "UserError",

    "target": "Copy Bronze - Excel to Parquet",

Cause:- This has to with the excel file having two or more columns with the same name. We had the field "COUNTRY_NAME" twice in the excel and this was causing the issue. 

Fix: Remove the duplicated field and re-process the file again.

Self Hosted Integration Runtime sharing between Synapse Workspaces

Scenario: We have to connect to an on-premise SQL Server/Oracle database and have three synapse workspaces in Azure, DEV, UAT and PROD in three different subscriptions. 

Current- Architecture

To connect to these sources on the on-premise networks we have virtual networks set up on Azure, gateways and site to site connections to local network.

The downside of this is having to maintain a lot more from an administrator perspective,

1. Three Virtual Networks 
2. Peering between three networks
3. Three seperate Virtual Machines to host the respective Azure Self Hosted IRs

Instead of this, we thought of simplifying it and having only one Virtual Network, one VM where self hosted integration runtime is hosted and the IR is shared by all three Synapse workspaces, see below architecture, 

Proposed Architecture with Shared Self Hosted IR.

Now comes the road-block here, sharing integration run-time is not yet available for Synapse Analytics ( reference - https://learn.microsoft.com/en-us/azure/synapse-analytics/data-integration/concepts-data-factory-differences#available-features-in-adf--azure-synapse-analytics  )

 

This seems to be something in the backlog for the Synapse Analytics Product team, I will be updating here if this comes out in any new releases. This will be really cool to have.