Calling Powershell scripts from Managed Instance SQL Agent jobs

Brett Green 20 Reputation points
2024-01-10T13:21:29.2566667+00:00

My company has decided Managed Instance is the Azure solution for them, over a traditional IaaS configuration. On our current OnPrem instance(s) we call many powershell scripts (that reside on local disk) from within our SQL agent jobs and also output results to files on local disk. Within an MI environment are we able to replicate this in any way, i.e setup a container on blob/storage account and grant read/write access to the SQL Agent service account ?

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. GeethaThatipatri-MSFT 29,517 Reputation points Microsoft Employee
    2024-01-10T16:30:54.4933333+00:00

    Hi, @Brett Green Welcome to Microsoft Q&A thanks for posting your question.

    SQL Agent PowerShell jobs are not supported, but are not blocked either. We know that few customers do use them, but we also know that it’s not fully working (e.g. you cannot install/import/update a PowerShell module). What you’ve described is not a pattern that’s supported, as accessing local file is not supported, even though it might happen to work.

    Below is the workaround you may try.

    If your PowerShell scripts are outputting CSV or Parquet files, you can use the CETAS (Create External Table As Select) feature in Azure SQL Managed Instance to export the data to Azure Blob Storage. CETAS allows you to create an external table that references data stored in Azure Blob Storage. CETAS feature 

    You can then use a SELECT statement to query the data and export it to a CSV or Parquet file in Azure Blob Storage Also a blog post here.

    It helps you export data that's an output of a SELECT statement into a storage account (ABS). This has nothing to do with PowerShell scripts writing data, but perhaps it still checks the box if you could export straight thru T-SQL?

    Another alternative would be to run ADF or some external solution that would pull out the data from SQL MI to a desired destination, rather than pushing the data out via SQL Agent.

    Regards

    Geetha

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.