1

I'm using SSIS and Powershell to check if a file is locked or not.

I have the below Expression in a variable called 'Cmd':

"-NoProfile -ExecutionPolicy ByPass -Command \"try { [IO.File]::OpenWrite(‘” + @[User::TestFilePath] + “‘).close();0 } catch {999}"

Which evaluates to this:

-NoProfile -ExecutionPolicy ByPass -Command "try { [IO.File]::OpenWrite(‘” + @[User::TestFilePath] + “‘).close();0 } catch {999}

Using the Execute Process Task I then call the Cmd variable above and have Success and Failure constraints after it. The Process always reports a Success, even I open the file in question, rename or even delete it.

If I then amend to the below, the Task will always fail, even if its not open:

"-NoProfile -ExecutionPolicy ByPass -Command \"try { [IO.File]::OpenWrite(‘” + @[User::TestFilePath] + “‘).close();exit 0} catch {exit 999}"

What am I missing?

Michael
  • 2,507
  • 8
  • 35
  • 71
  • Not sure on PowerShell, but this is my [goto answer](https://stackoverflow.com/a/937558/181965) for checking file status – billinkc Nov 21 '18 at 14:50
  • A Powershell version of a function to test for file lock can be found [here](https://stackoverflow.com/questions/24992681/powershell-check-if-a-file-is-locked) – Theo Nov 21 '18 at 15:37
  • Hello, thank you for the link. However SSIS Process task only accepts an Integer as a Success\Fail value - I think that is the issue but am unsure how to amend the scripting – Michael Nov 21 '18 at 15:54

1 Answers1

1

If it helps anyone, I found a resolution. Instead of calling the PS script via an expression, I just called the actual PS file via a Process Task with the below in in the PS file:

$file = "\\xxxx\xxxxx\xxxxxxxx\test.log"
try { [IO.File]::OpenWrite($file).close();exit 0 } catch { exit 999}
Michael
  • 2,507
  • 8
  • 35
  • 71