Can I run a Powershell script inside Azure Synapse Pipelines?

Question

I have seen numerous posts about using powershell to run Azure Synapse Pipelines but I am looking for a method to execute a Powershell script within Azure Synapse Pipelines.

The use case is that we currently have a Powershell script that queries our Active Directory and then dumps a .csv into a datalake in Azure once day. We then transform that file and use it in an Azure SQL Database. THis goes from Raw (.csv) -> Processed (.parquet) -> Curated (.parquet). The problem is that since the powershell script overwrites the previous file, the only file history we have is in the Processed as we keep the Curated to a single file.

Any thoughts on how to do this?

Answer 1

Hi @Richards, Sam (DG-STL-HQ) ,

Welcome to Microsoft Q&A platform and thankyou for posting your query.

As I understand your requirement here, it seems like you want to execute powershell script via Azure synapse pipeline. Also , you want to avoid the overwriting of files and want to keep all the history files in the curated zone as well. Please correct me if my understanding is incorrect.

To achieve the desired output, you can call Custom activity in your azure synapse pipeline to run the poweshell script. Please go through the following blog to get full details on how to do that : How To Run PowerShell Script in Azure Synapse analytics

Once the file lands in the ADLS , you can use copy activity to copy the file within ADLS from one folder path to another. If you mention the hardcoded filename in the sink dataset, it would overwrite the existing file, however , as you want to maintain history as well. So , you can parameterize the filename based on the current time or else leave the filename as blank so that it would automatically assign a new filename each time pipeline gets executed.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators