MSBUILD Executing only Changed SQL Scripts

Question

I need to construct an MSBUILD script executes .SQL Scripts which have changed since last build.

I initially thought that I could copy all scripts from one folder to another using the <Copy> task and using the CopiedFiles <Output> for the copy task. However the copy task returns All files that it Attempted to copy, not actual copied files.

I am able to get MSBUILD to execute SQL Scripts via MSBUILD.ExtensionPack but Im scratching my head on this one

Answer 1

Could it be that you copying into an empty destination?

SkipUnchangedFiles

If true, skips the copying of files that are unchanged 
between the source and destination. The Copy task considers 
files to be unchanged if they have the same size and the 
same last modified time.

In your case i suspect that all files are considered changed since they don't exist at the destination.

Answer 2

You can do this with a concept known as incremental building. The idea is that you would create a target and then specify the inputs and outputs, which would be files. MSBuild will compare the timestamps of the input files to the output files. If all outputs were created after all outputs then the target is skipped. If all inputs are newer then all the target will be executed for all files. If only a portion are out of date, then only those will be passed to the target. For more info on this see the section Using Incremental Builds in my article Best Practices For Creating Reliable Builds, Part 2.

Also for more resources on MSBuild I have compiled a list at http://sedotech.com/Resources#MSBuild

Answer 3

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="RunScripts">

  <Import Project="$(MSBuildExtensionsPath)\ExtensionPack\MSBuild.ExtensionPack.tasks"/>

  <PropertyGroup>
    <ConnStr>Server=Example;Database=Example;Trusted_Connection=True</ConnStr>
    <BuildFolder>Build\</BuildFolder>
  </PropertyGroup>

  <ItemGroup>
    <Scripts Include="*.sql"/>
  </ItemGroup>

  <Target Name="RunScripts"
          Inputs="@(Scripts)"
          Outputs="@(Scripts->'$(BuildFolder)%(Filename)%(Extension)')">
    <SqlExecute TaskAction="ExecuteScalar"
                Files="@(Scripts)"
                ConnectionString="$(ConnStr)"/>
    <Copy SourceFiles="@(Scripts)"
          DestinationFiles="@(Scripts->'$(BuildFolder)%(Filename)%(Extension)')"/>
  </Target>
</Project>