Forum Discussion

GlynLamb's avatar
GlynLamb
Copper Contributor
Aug 18, 2021

Script works in powershell and command line but fails from SQLAgent job step

I have a script using DBAtools cmd Get-DbaDatabase -sqlInstance $Inst which converts to dbaDataTable and writes to a SQL ServerInstance. If I run the script from Powershell ISE works without issue

If I run from Command promt :

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "<location>\<Get-DbaDatabase.ps1>"

completes without issue

however If I run from a SQLAgent job step

type : PowerShell

Runs As : SQL Server Agent Service Account

Command : C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "<location>\<Get-DbaDatabase.ps1>"

I get a bulk load error

WARNING: [07:05:37][Write-DbaDbTableData] Failed to bulk import to <database location>.<tablename> | The given ColumnMapping does not match up with any column in the source or destination.

the same powershell script is called for all runs

any ideas

Thanks in advance

Glyn

 

 

 

sql server

2 Replies

  • shakyknees's avatar
    shakyknees
    Copper Contributor
    Jan 12, 2022

    GlynLamb I have been working through a somewhat similar problem.  To debug it, I setup a test version of the script that uses a different (new) output table using  Write-DbaDbTableData -AutoCreateTable, then compare the resulting new table schema to what is expected.  That helped me.  But as to differences in Powershell behavior between command line vs SQLAgent, my only experience is that I can't use the "Powershell" jobstep defined by SQLAgent, rather must use "Operating System (CmdExec)" to control the Powershell version in use.  Which you are probably already doing. 

Resources