Can I run a Powershell script inside Azure Synapse Pipelines?

Richards, Sam (DG-STL-HQ) 151 Reputation points
2022-04-15T13:58:29.493+00:00

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?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,430 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 31,731 Reputation points Microsoft Employee
    2022-04-15T14:39:48.853+00:00

    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 130616-image.png or upvote 130671-image.png 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

0 additional answers

Sort by: Most helpful