Synapse Devops deployment error - service principal expiration

Issue: Deploying code from development environment in synapse to higher environments using Azure Devops failed due to the below error 

Encountered with exception:Error: Get workspace location error: Could not fetch access token for Azure. Verify if the Service Principal used is valid and not expired. For more information refer https://aka.ms/azureappservicedeploytsg

An error occurred during execution: Error: Get workspace location error: Could not fetch access token for Azure. Verify if the Service Principal used is valid and not expired.

Cause: If you are using Azure Devops, you should know that Azure devops needs access to all the environments you are moving code and it uses a service principal to get access to the resources. When your IT admin creates a service principal which is used for Azure Devops, they normally set up a period till when the secret expires. For us we went over the threshold and hence the error. 

Resolution: Asking your Windows AD/IT admin to create a new passcode value and using it in your devops by following below steps

Go to Azure Devops

Navigate to Project Settings > Pipelines > Service Connections and then to edit connection and this opens up a promt as shown below, here update the service prinicpal key you received from your Windows AD/Entra Admin, paste, verify and save it.

Once you have given the new service principal key, we should be ready to start deploying again. 

Working with com.crealytics.spark.excel package for excel files in Azure Synapse

This is a post to help atleast some of you who is trying to get the com.crealytics.spark.excel package up and running in your synapse workspace and on your spark pool. I will try to explain it in the most simplest of steps. 

Step 1 - Go to MVN repository and download the latest jar file for the crealytics excel spark package.

Step 2 - Once the file is downloaded go to your Synapse workspace and to the Manage tab, then to the Workspace packages tab

Step 3 - Upload jar file to workspace packages and it should up  on the list with provisioning status as succeded, see below.

Step 4 - Once the package is uploaded, go to Manage > Sparkpool > Packages and select the spark-excel_2.12-3.5.0_0.20.3.jar from the list. Important that session level packages are allowed and the spark pool is restarted after this step. See screenshots below,

That's it on the configuration side, now on your notebook, you could have a code snippet like below to read from an excel file.

df = spark.read.format("com.crealytics.spark.excel").option("header", "true")

/ .option("inferSchema", "true").load(ReadPath)

where ReadPath contains the path to the excel in your datalake. You can play around with more options on this piece of code. Hope this helps, please let us know in comments.

Note:- If you have higher environments, make sure you repeat the steps there.