Passing a variable in sqlpackage containing filepath

Question

so i am trying to exectue a ps script directly after tfs build. while the script runs post-build there is an error that says sourcefile must have a valid .dacpac extension. Below is the code

#Powershell script to capture the latest version of the dacpac generated for the source file
$srcdir= get-childitem '\\a\b\c\d\e\f' | Where {$_.LastWriteTime} | select -last 1
$srcpathitem= Join-Path \\a\b\c\d\e\f$srcdir
$filesource= get-childitem -path $srcpathitem -filter abc.dacpac
$finalsource= Join-Path $srcpathitem $filesource
#Latest version of the target file dacpac created
$tgtdir= get-childitem '\\1\2\3\4\5\6' | Where {$_.LastWriteTime} | select -last 1
$tgtpathitem= Join-Path \\1\2\3\4\5\6 $tgtdir
$filetarget= get-childitem -path $tgtpathitem -filter EFT.dacpac
$finaltarget= Join-Path $tgtpathitem $filesource
&"\\xyz\build\xyz\xyz\Scripts\DAC\bin\SqlPackage.exe" /a:Script `
/Sourcefile:$finalsource `
/TargetFile:$finaltarget `
/op:"\\xyz\build\xyz\xyz\Scripts\123-script.sql" `

Sorry the filelocation and folder names have been given aliases due to privacy concerns.

so the third from the last is the place where the error for dacpac is coming. so pls tell how to pass the filepath so that there isn't any error. and also FYI the path cannot be hardcoded as the folder has newer versions created after some hours automatically.

Answer 1

Try to use

/Sourcefile:$(finalsource)`
/TargetFile:$(finaltarget)`

instead of

/Sourcefile:$finalsource `
/TargetFile:$finaltarget `

Answer 2

I assume your following code can get the file successfully:

$filesource= get-childitem -path $srcpathitem -filter abc.dacpac

Then if you write out "$filesource" in powershell, you can find that the value is just "abc.dacpac'. So you can try to update "/Sourcefile:$finalsource" to "/Sourcefile:$finalsource.FullName" which will return the entire file path.