THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SSIS MSBuild task now included in MSBuild Extension Pack

The information in this blog post may be of interest to anyone out there that is using Continuous Integration with msbuild in order to build/deploy a project that includes SSIS packages.

The MSBuild Extension Pack at http://msbuildextensionpack.codeplex.com/ now includes an MSBuild task that will build an SSIS project, the output from which is a .deploymentmanifest file that can be used in concert with the SSIS deployment utility.

Building a SSIS project using the SSIS MSBuild Task essentially has the same effect as right-clicking on a SSIS project and selecting Build with CreateDeploymentUtility=True (i.e. produces a .deploymentmanifest file):

build ssis project SSIS Deployemnt Utility

The advantage of using the MSBuild task is of course that you can now do it in an MSBuild script, none of this manual mouse-clicking required thank you very much!

The SSIS MSBuild task was previously available in the SSIS community samples project at http://sqlsrvintegrationsrv.codeplex.com but having it as part of the MSBuild Extension Pack is definitely advantageous as this is a very widely used set of utilities. The source code has been available in the MSBuild Extension pack since changeset 54481 which was checked-in on 26th August 2010 and was included in the August 2010 Release.

Note that in order to deploy your packages after using the SSIS MSBuild task you will need to call the SSIS deployment utility and pass in the outputted .deploymentmanifest file. Information on doing that is decidedly thin on the ground so I’ll try and elucidate. The SSIS Deployment Utility is an executable that gets installed with SQL Server Integration Services workstation tools and is called dtsinstall.exe. You can call it on the command-line and pass in the path to a .deploymentmanifest file but as far as I can determine there is no way to do an unattended install (i.e. there is no way to tell dtsinstall.exe where to install the packages to, it simply opens up a GUI and prompts you for this information). This is a horribly antiquated method of deployment and I’m hunting around to see if there’s anything I’ve missed here – if you know any better please let me know.

Hope this helps!

@Jamiet

Published Tuesday, September 14, 2010 11:11 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nathan Griffiths said:

I don't think you can automate deployment to the server using a deployment manifest and dtsinstall, you could maybe use the DTUtil.exe for that by calling it for each package in the deployment folder?

September 14, 2010 6:40 PM
 

jamiet said:

Nathan,

Yeah that's what I figured. Which, it seems to me, kinda defeats the purpose of the MSBuild task.

Bizarre.

-Jamie

September 15, 2010 1:35 AM
 

piers7 said:

I've always just bypassed the manifest and used SMO to upload the packages. In which case all the CI build actually needs to do is copy the DTSX packages from the project source tree to the output directory (there's no actual *building* going on anyway).

So whilst the MSBuild task is useful, in that it replaces the 'copy DTSX to output directory' part of the process above, I'll almost certainally stick to PowerShell and SMO for deployment due to the flexibility / unattended install.

September 18, 2010 8:04 AM
 

William Dwyer said:

I can't seem to find any msbuild task for SSIS in this extension set you refer to, is there something i'm missing? Is it a sql server database build task that will work with ssis perhaps?

January 18, 2013 6:13 PM
 

Yong said:

Hi William

If you check MSBuild.ExtenstionPack.tasks file in Solutions\Main\Common, you can find the following.

  <UsingTask AssemblyFile="$(ExtensionTasksPath)MSBuild.ExtensionPack.dll" TaskName="MSBuild.ExtensionPack.SqlServer.BuildDeploymentManifest"/>

This is the task name that you should use in the MSBuild script. However, integration with the SSIS community samples project is only done for 2008. Therefor this will only create SSISDeploymentManifest file. Integration for 2012 hasn't done yet. For more information please check out the following link.

http://speaksql.wordpress.com/2013/06/07/a-journey-to-db-deployment-automaton-ssis-build-using-msbuild/

June 8, 2013 10:06 AM
 

James Page said:

I am using a PowerShell script (with SMO) to deploy from the ispac file.

The script creates a folder in ICS if needed, deploys to ICS and sets any project parameters that may be needed based on the environment.

I am doing this because I need to deliver SSIS projects to other sites to be installed by a non-developer.

July 31, 2013 10:55 AM
 

William Todd Salzman said:

Jamie,  Have you done any more work on this?  I followed the instructions in Yong's comment above and built the dll for Visual Studio 10, but now I am trying to work out how to take the next step and get this automated in a TFS Build server as you did for SSDT database projects in another post.  Any insight in how to take the dll and use it?  What in the build definition tells the server what type of build to use?  The post Yong mentions uses the command line and a pre-built build proj file.

August 30, 2013 3:36 PM
 

jamiet said:

Hi William,

Apologies for taking so long to reply.

To be honest its so long since I wrote this (and equally so long since I attempted what is described here) that I'm not too sure. I suspect the compiled DLL will have to be a part of your code base - that way you can reference it from an msbuild script and know for sure that it will exist.

"What in the build definition tells the server what type of build to use"

I'm afraid I'm not sure what you mean by "type of build". Can you elaborate?

thanks

Jamie

September 5, 2013 6:29 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement