Edit: MSSISBuild has been updated and moved to GitHub. Read about it here. The rest of this post is still relevant, it’s just where you download the code form that has changed. this has proven to be a popular post, and the software has been downloaded a few times. I will post an update for SQL Server 2016 soon, though I think it is just references in the solution that needs updating.

Today I am pleased to announce the release of MSBuildSsis2012 on Codeplex. But before i get into what it does first, a bit of background:

Back in May, and subsequently a few more times since, I’ve posted about an error I get occasionally in one of our custom tasks that run in our builds. This custom task is a special case: As the SSIS 2012 project extension is dtproj, it cannot be compile using MSBuild. The typical solution would be to use DevEnv In MSBuild. And to automate deployment the solution (that would work for most people) is to use the SSIS Deployment Wizard in silent mode. However, we don’t use the SSIS Deployment Wizard in our automated builds as it does not work in Silent Mode when you have assembly references in the SSIS packages: it destroys those references and the dtsx packages fail whenever they run.

So for all our builds we use two custom tasks; one to compile the .dtproj and the other to deploy our SSIS 2012 solution. This custom task calls an assembly that was created using some sample code provided by the Microsoft team. The source of this version can be found on CodePlex.

The error to which I am referring to was a timeout issue that occurred intermittently and was fixed with increasing the default CommandTimeout property on the SQLCommand class. More in depth in my original blog post SSIS 2012 Deployment Fail Revisited.

If you followed that link you’ll see that I provided the changes I made to the source code in the DeployProjectToCatalogTask class. Recently more changes were made to our custom task to take advantage of Environments for package execution. Briefly, environments provide environment specific values for parameters within SSIS packages. Obvious advantages are:

At any rate, the changes I’ve made pertain to automating the creation and deployment of an environment in the same manner that a project catalog folder is created. Note it does not include adding the environment variables (the correct way to do that would be to update the SSISDB post deployment using a SQL Script [1]).

Rather than posting the source code here I’ve created a CodePlex solution, so you can include the dll in your build binary folder. I’ve stripped back all the other Integration Services samples and left just the 2 classes that I use to compile (DeploymentFileCompilerTask) and deploy (DeployProjectToCatalogTask). w/r/t the previous changes I’ve made, in the DeployProjectToCatalogTask you will see the CommandTimeout property added to the SQLCommand class and set to 300 seconds, up considerably from the default 15 seconds.

For info on how to write a Custom Task, this article is a good place to start. But I’ll include a very quick guide; in your build.proj file, include the name of the tasks and reference to the assembly file via the UsingTask element.

<UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft.SqlServer.IntegrationServices.Build.dll"/>
 
<UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft.SqlServer.IntegrationServices.Build.dll"/>

Run the task by inserting it into the desired target: let’s say that in your build you want to build the SSIS project before any other project and you want to deploy it straight after every other solution has been compiled, it would look something like this:

<UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft.SqlServer.IntegrationServices.Build.dll"/>
 
<UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft.SqlServer.IntegrationServices.Build.dll"/>

<Target Name="BeforeCompile">
<DeploymentFileCompilerTask InputProject="DailyDataImports.dtproj" Configuration="Debug" ProtectionLevel="DontSaveSensitive" RootOutputDirectory="$(BuiltFiles)">
</DeploymentFileCompilerTask>
</Target>
 
<Target Name="AfterCompile">
<DeployProjectToCatalogTask DeploymentFile="DailyDataImports" Instance="SSISBox01" Folder="SSISProjects" CreateFolder="true" Catalog="SSISDB" Environment="Development" />
</Target>

All you really need to do is replace the arguments with your own values. You cna hard bake them in, although I recommend defining them through a set of properties using PropertyGroup element, or as an ItemGroup element for batching if you have more than one solution. I also recommend adding a few messages around the targets so that finding any errors is less painful.

I sure hope people find this as useful as I’ve found it. You can use this site or CodePlex to ask questions/comments/criticisms etc.

[1] Put simply, post deployment you can execute a Sql Script (in the build use “Sqlcmd” via “Exec Command” element) to create a variable if it does not exist using the sprocs in SSISDB:

DECLARE @var sql_variant = N'foo'
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'bar', @sensitive=False, @description=N'description of the variable ', @environment_name=N'Development', @folder_name=N'SSISProjects', @value=@var, @data_type=N'String'
GO

and if it does exist, then you call the sprocs below;

EXEC [SSISDB].[catalog].[set_environment_variable_property] @variable_name=N'bar', @property_value=N'the description of the paramter', @environment_name=N'Development', @folder_name=N'SSISProjects', @property_name=N'Description'
DECLARE @var sql_variant = N'foo'
EXEC [SSISDB].[catalog].[set_environment_variable_value] @variable_name=N'bar', @environment_name=N'Development', @folder_name=N'SSISProjects', @value=@var
GO