Invoke-SqlCmd needs Working Directory

[JonDavis-AZED]

When using Sql Query File and for Sql File a path to a file, the working directory for the executed .sql file should be the path of the referenced file by the time SQLCMD is executed. Otherwise, the working directory should be an input.

The intent is that :r .\NestedFolder\NestedFile.sql should be supported for the executing .sql file, where .\ is understood to be the containing directory of the executing .sql file.

[Ajay-MS]

@JonDavis-AZED

There is a logical separation between the execution directory and the working directory. .\ is being used for the execution directory. That's why $(System.DefaultWorkingDirectory) the variable is being required for the working directory.

[stimpy77]

Are you saying that variable is available from in the script? Was it recently made available? Is it documented? I wouldn't have posted an issue if I found documentation. That's why I stated, "Otherwise, the working directory should be an input." I would expect (require) the value to be related to the folder containing the script or otherwise the TFS branch root or somesuch that can be worked with predictably.

[Ajay-MS]

@stimpy77

Here is the documentation - Pre defined variables

One way of looking at the solution. You can pass a variable to the script from the task with value as $(System.DefaultWorkingDirectory) Now in the script, you will receive the value of defaultDirectoryPath and using which you can load the nested scripts.

As a direct solution, I will check with nested scripts with .\ and will update you.

[stimpy77]

I see now an "Additional Arguments" field that I perhaps did not see when I logged this issue (it's been months now). I assume that is where the variable can be propagated. If that's where that goes, that would be a workaround to the issue. The issue would remain.

Absent a working directory input the working directory ( .\ ) should default where the script file is or else the tfs project root rather than where the EXE process is. The working directory is not specified in the task as an input, which is what I meant by "Otherwise, the working directory should be an input.".

[stimpy77]

[eaarora-ms]

@stimpy77 We understand your concern. I have marked this as an enhancement and will let you know when the feature is available.