When it comes to MSBUILD and SSIS projects you unfortunately are told that this project type is not supported. In order to create builds that include SSIS projects you must use the Integration Services build component from the MS SQL Server Community samples. You can download the latest version here:

https://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest

You have to compile this yourself – you cannot just download the dll. However you will probably find that the project does not build out of the box because of missing dll references. This is because the project was created with hardcoded hint paths for an older version of visual studio/sql server.

Use this guide to correct the problem.

Once downloaded extract the zip file on your local PC and open the \main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj file in Visual Studio (I’m using 2013 – any post 2010 version should work).

If using VS2012 you will be prompted to remove the source control bindings which is fine.

Once opened if you view the references in Solution Explorer you will see a bunch with yellow warnings indicating that they are missing like this:

Missing Dll Files

Missing Dll Files

The reason for this is that the project has hard coded hint paths to the assemblies folder. To correct this issue we must edit the project file. To do this right click on the project in solution explorer (the highlight level in the above screenshot) and select “Unload Project”.

Right click the project again and select “Edit project file”. An xml file will load, locate this section:

<ItemGroup>
<Reference Include="Microsoft.AnalysisServices.Project">
<HintPath>C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL</HintPath>
</Reference>
<Reference Include="Microsoft.Build.Framework" />
<Reference Include="Microsoft.Build.Tasks.v4.0" />
<Reference Include="Microsoft.Build.Utilities.v4.0" />
<Reference Include="Microsoft.DataTransformationServices.VsIntegration">
<HintPath>C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL</HintPath>
</Reference>
<Reference Include="Microsoft.DataWarehouse.VsIntegration, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
<HintPath>..\..\..\..\..\..\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL</HintPath>
<SpecificVersion>False</SpecificVersion>
</Reference>

Change the highlight paths to:

<ItemGroup>
<Reference Include="Microsoft.AnalysisServices.Project">
<HintPath>$(VS120COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.AnalysisServices.Project.DLL</HintPath>
</Reference>
<Reference Include="Microsoft.Build.Framework" />
<Reference Include="Microsoft.Build.Tasks.v4.0" />
<Reference Include="Microsoft.Build.Utilities.v4.0" />
<Reference Include="Microsoft.DataTransformationServices.VsIntegration">
<HintPath>$(VS120COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataTransformationServices.VsIntegration.DLL</HintPath>
</Reference>
<Reference Include="Microsoft.DataWarehouse.VsIntegration, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL">
<HintPath>$(VS120COMNTOOLS)\..\IDE\PrivateAssemblies\Microsoft.DataWarehouse.VsIntegration.DLL</HintPath>
<SpecificVersion>False</SpecificVersion>
</Reference>

This change uses an environment variable to point to the Visual Studio installation folder. The 120 part of the variable specifies the Visual Studio 2012 installation folder even though I’m using Visual Studio 2013. This is because when you install SQL Server 2012 Tools, which includes the dev SDK they are installed in the VS2012 shell. Future versions will use different folders so you may have to up that 120 number based on your version. Unfortunately there is no other environment variable that points to where SQL installed these files.

Once complete – save the file and close it. Then right click the project again and click “Reload”.

Now we need to fix the yellow warning on the key.snk. This is a signing file which must always be recreated when taking a copy of a project.

Right click the project file again and select properties. Go to the “Signing” tab. From the drop down select “”:

keyfile

In the popup create the file:

createkeyfile

Press ok.

You can now delete the old key.snk file from solution explorer:

deletekeyfile

Your project should now be ready to go. Click “Build Solution” from the Build menu. In the lower left hand corner you should now see “Build Succeeded”!

Now you need the dll for use in your project. This can be found in the bin folder of your solution:

\main\SSISMSBuild\Project\bin\Debug\Microsoft.SqlServer.IntegrationServices.Build.dll

Happy building.

Tagged with →  

Leave a Reply

Your email address will not be published. Required fields are marked *