I have a Powershell script that gets a list of files from a SQL PROC.
For each file, it then moves a file, then updates a DB record, then inserts a DB record.
How can I encapsulate the whole process (IO File Operation, plus the two SQL operations inside a Powershell transaction?
So if the file move fails, the subsequent SQL UPDATE and INSERT don’t happen, and if either the UPDATE or INSERT fail, the preceding file move gets rolled back?
I’m familiar with pure SQL transactions but I’ve pretty new to Powershell.
I’m using the Move-Item cmdlet to do the file operation which I believe supports Powershell transactions, and I’m using System.Data.SQLClient.SQLCommand to access SQL, although I was and could go back to using Invoke-SQLCmd if needed.