0

Need more help from you brilliant folks.

I am trying to import a CSV file to a SQL table with this code:

$SQLServer = "MYSQLINSTANCE"
$SQLDatabase = "DBATools"
$SQLString = "Server=$SQLServer;Database=$SQLDatabase;Integrated Security=True;"
$SQLTable = "dbo.MyTable"
$CSVFile = "\\remotesrvr\OutputShare\Output.csv"
$Header = 'RecordedDTM','TablespaceName','PhysicalFileName','MaxSize','UsedSize','FreeSize','PctUsed'
Import-CSV -Path $CSVFile -Header $Header | Select-Object -Skip 1 | ForEach-Object {Invoke-SqlCmd -ConnectionString $SQLString -Query "INSERT INTO $SQLTable VALUES ('SVPPDRMDB01','$($_.RecordedDTM)','$($_.TablespaceName)','$($_.PhysicalFileName)',$($_.MaxSize),$($_.UsedSize),$($_.FreeSize),$($_.PctUsed))"}

It runs successfully in a Powershell prompt, but when I create a SQL job of type Powershell, I get the following error:

Unable to start execution of step 1 (reason: line(7): Syntax error).  The step failed

Not a lot of help, huh. What am i missing?

Improve this question
3
  • 2
    See this answer about SQL Agent tokens. Specifically, related to your use of $($_.RecordedDTM) syntax.
    – Scott Hodgin - Retired
    Commented Nov 5, 2019 at 17:35
  • 5
    I generally recommend against using the PowerShell job step type. Instead use a CmdExec step to call PowerShell to call your script. That extra layer turns out to resolve a lot of confusion around PowerShell job steps being special
    – AMtwo
    Commented Nov 5, 2019 at 21:40
  • Well, that just stinks. But maybe Microsoft has good reasons for it to work the way it does. I did end up running the script outside SQL Server just like the comment above stated.
    – Darrell
    Commented Nov 6, 2019 at 18:16

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.