Running PowerShell Script within SQL Agent

[meachcarnahan]

Hey Everyone! I was just wondering if someone would be able to help me with a powershell / sql agent job. I was following this thread here https://dbatools.io/agent/ and for the most part it works brilliantly. I am running into a issue where when I email the results from a simple powershell window I get the full transcript of commands, output and corespondance. However when i run the same .ps1 script from the sql agent i only get pieces of information emailed to me.

The script:
Essentially I have a powershell script that connects to an azure sql database, backs it up locally to a bacpac file, then refreshes/imports the database onto a local databases on premise.

After following some instruction from https://dbatools.io/agent/, I've created a proxy account, a credential, and an agent job that executes as CMD that calls the ps1 script

Output From Powershell window yields the results I want emailed to me as follows:

---

Windows PowerShell transcript start
Start time: 20210219170150
Username: ************************
RunAs User: ************************
Machine: ************************
Host Application: C:\WINDOWS\system32\WindowsPowerShell\v1.0\PowerShell_ISE.exe
Process ID: 6068
PSVersion: 5.1.14393.3866
PSEdition: Desktop
PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.14393.3866
BuildVersion: 10.0.14393.3866
CLRVersion: 4.0.30319.42000
WSManStackVersion: 3.0
PSRemotingProtocolVersion: 2.3
SerializationVersion: 1.1.0.1

---

Transcript started, output file is E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\refresh_Q9-DBMGMT01_ZediPrd_20210219050150.log

Backing up database to:....
E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\AzureSQLBackup_ZediPrd-20210219050150.bacpac
Connecting to database 'ZediPrd' on server '************************'.
Extracting schema
Extracting schema from database
Resolving references in schema model
Validating schema model
Validating schema model for data package
Validating schema
Exporting data from database
Exporting data
Processing Export.
Processing Table '[dbo].[ZediData]'.
Processing Table '[dbo].[ZediDataQTR]'.
Successfully exported database and saved it to file 'E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\AzureSQLBackup_ZediPrd-20210219050150.bacpac'.

Using latest bacpac file:
E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\AzureSQLBackup_ZediPrd-20210219050150.bacpac

Database ZediPrd not found on ************************. Importing Database...

Importing to database 'ZediPrd' on server '************************'.
Creating deployment plan
Initializing deployment
*** The compatibility level of the source schema 150 is not supported, which may result in undefined behavior. Please upgrade to a later version which supports this compatibility level.
*** The compatibility level of the target schema 150 is not supported, which may result in undefined behavior. Please upgrade to a later version which supports this compatibility level.
*** The compatibility level of the source schema 150 is not supported, which may result in undefined behavior. Please upgrade to a later version which supports this compatibility level.
Verifying deployment plan
Analyzing deployment plan
Importing package schema and data into database
Updating database
Importing data
Processing Import.
Disabling indexes.
Processing Table '[dbo].[ZediData]'.
Processing Table '[dbo].[ZediDataQTR]'.
Enabling indexes.
Successfully imported database.

---

Windows PowerShell transcript end
End time: 20210219170225

---

Output of results when the same ps1 script is ran through the sql agent job (not desired):

---

Windows PowerShell transcript start
Start time: 20210219181452
Username: ************************
RunAs User: ************************
Machine:************************
Host Application: powershell.exe -File C:\Scripts\powershell\refresh_AzureDb_to_OnPrem.ps1
Process ID: 1408
PSVersion: 5.1.14393.3866
PSEdition: Desktop
PSCompatibleVersions: 1.0, 2.0, 3.0, 4.0, 5.0, 5.1.14393.3866
BuildVersion: 10.0.14393.3866
CLRVersion: 4.0.30319.42000
WSManStackVersion: 3.0
PSRemotingProtocolVersion: 2.3
SerializationVersion: 1.1.0.1

---

Transcript started, output file is E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\refresh_Q9-DBMGMT01_ZediPrd_20210219061452.log

Backing up database to:....
E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\AzureSQLBackup_ZediPrd-20210219061452.bacpac

Using latest bacpac file:
E:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017DBA\MSSQL\Backup\AzureSQLBackup_ZediPrd-20210219061452.bacpac

Database ZediPrd found on************************. Dropping database before refreshing Database...
.
.
.

---

Windows PowerShell transcript end
End time: 20210219181543

---

Can someone help me with sending the entire transcript to the log? Is this because i am also running the DAC package for sqlpackage to export and import data tier appllications?

Thanks in advanced!