How to have MSBuild process all SSIS projects

Question

I have successfully followed the blog 1 to get MSBuild to build an SSIS project. This involved creating a dll and an MSBuild script. The process works correctly for any single project file.

I'm looking for help in getting this process to work for each .dtproj file that is defined in a solution (.sln) file. I have read about MSBuild Batching 2 but it refers to items that are defined in the build script. I want it to work with whatever projects are in the solution file so I don't have to manually edit a script every time a project is add/removed/moved.

Any suggestions or links to approaches? Thanks for taking the time to read my question!

blog

MSBuild Batching

Answer 1

If you don't want to with the batch option you can try something like this:

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

  <PropertyGroup>
    <MySolution Condition="'$(MySolution)'==''">MySolution.sln</MySolution>
    <Configuration Condition="'$(Configuration)'==''">Release</Configuration>
    <SSISServer Condition="'$(SSISServer)'==''">MyServer</SSISServer>
    <PROJECTNAME Condition="'$(PROJECTNAME)'==''">MyProjectName</PROJECTNAME>
  </PropertyGroup>

  <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  
  <Import Condition="Exists('$(MySolution).metaproj')" Project="$(MySolution).metaproj"/>
  
  <Target Name="Initialize">
    <MSBuild Projects="$(MySolution)" Properties="Configuration=$(Configuration);MSBuildEmitSolution=1" Targets="Clean" />
  </Target>
  
  <Target Name="Build">
    <Message Text="**************Building SSIS project: %(ProjectReference.FullPath) for configuration: $(Configuration)**************" />
    <DeploymentFileCompilerTask
        InputProject="%(ProjectReference.FullPath)"
        Configuration="$(CONFIGURATION)"
        ProtectionLevel="DontSaveSensitive">
    </DeploymentFileCompilerTask>
  </Target>

  <Target Name="Deploy">
    <Message Text="**************Deploying SSIS project: %(ProjectReference.FullPath) for configuration: $(Configuration)**************" />
    <Message Text="..\%(ProjectReference.RootDir)%(ProjectReference.Directory)\bin\$(CONFIGURATION)\%(ProjectReference.FileName)%(ProjectReference.Extension).ispac"/>

    <DeployProjectToCatalogTask
        DeploymentFile="..\%(ProjectReference.RootDir)%(ProjectReference.Directory)\bin\$(CONFIGURATION)\%(ProjectReference.FileName)%(ProjectReference.Extension).ispac"
        Instance="$(SSISServer)"
        Folder="$(PROJECTNAME)"
        CreateFolder="true"/>
  </Target>
</Project>

This will generate a MsBuild project version of the solution including all the included projects (read about msbuildemitsolution), after that it will import the generated "msbuild solution" (.metaproj) and use the ProjectReference items to execute them one by one using the "DeploymentFileCompilerTask" and "DeployProjectToCatalogTask"

You can call this like this:

For Build:

msbuild SSIS.proj /t:Initialize

msbuild SSIS.proj /t:Build

For Deploy:

msbuild SSIS.proj /t:Initialize

msbuild SSIS.proj /t:Deploy

The Initialize target will generate the "msbuild solution" and you need to call this target to ensure the content is updated.

You can pass any of the Properties defined at the beginning on the command line to use the script for different solutions.

If you need different "Folders" for each project, you could use the "Filename" item metadata (i.e. %(ProjectReference.Filename) instead of $(PROJECTNAME)).

Note: This is not a final working script, but it should give a clear idea of the proposed solution, if you comment the "DeploymentFileCompilerTask" and "DeployProjectToCatalogTask" nodes of the script you can execute it and see some messages in the console with the data that will be processed.