Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Daniel — Mon, 28 Jan 2013 14:54:30 GMT

Great example!

It would be helpful to see similar examples for SSRS/SSIS/SSAS.

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

jamiet — Mon, 28 Jan 2013 15:02:33 GMT

"It would be helpful to see similar examples for SSRS/SSIS/SSAS."

You bet it would. When you find someone willing to do that, let me know :)

The problem you have with SSIS/SSAS/SSRS is that they do not use msbuild for build/deployment. SSDT projects *do* use msbuild and hence why the above steps work.

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Kyle Hale — Tue, 29 Jan 2013 02:08:31 GMT

Hey Jamie,

Great tutorial, I learned a lot!

Can you make a note that when you're connecting to the TFS server that is an HTTPS connection, not HTTP? Took me a bit too long to figure out why it would never connect to the TFS service ...

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

jamiet — Tue, 29 Jan 2013 07:22:26 GMT

Hi Kyle,

Good point. I have updated accordingly.

regards

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Aaron Lowe — Tue, 29 Jan 2013 14:23:00 GMT

We actually created a custom build definition to include the IS/RS/AS projects. As stated there's nothing to actually "build" it is merely a deployment process. However including that deployment allows to to do other tasks such as run IS jobs or Coded UI tests for RS. Hope to be blogging about it soon.

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Aaron Lowe — Tue, 29 Jan 2013 14:24:15 GMT

BTW (sorry I didn't mention it the first time). Another excellent post Jamie!

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

piers7 — Fri, 22 Feb 2013 04:10:10 GMT

I've previously done the same for SSAS, SSIS and SSRS under Sql 2008 R2. Basically you have to shell out to devenv.com to get the projects built, then work out how to take the output artefacts and deploy them.

For SSAS you have to use the Microsoft.AnalysisServices.Deployment.exe to convert the .asdatabase file into a MDX script (see http://piers7.blogspot.com.au/2012/05/analysis-services-2008-r2-breaking.html)

For SSIS you can just collect the .dtsx's from the project output directory

For SSRS I wrote a script to walk the project file and locate the items to be deployed

I'm busy setting all this up on a SQL 2012 project right now, so I'll see what's changed. Little from an MSBuild perspective, sadly.

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

jamiet — Fri, 22 Feb 2013 09:02:50 GMT

Hi Piers,

Yeah, nothing has changed from an msbuild perspective. However, a member of the SSIS team has produced an open source assembly that contains some msbuild tasks for building/deploying SSIS2012 projects - its called Microsoft.SqlSever.IntegrationServices.Build.dll.

The source is on Codeplex somewhere but I can't for the life of me find it. Get in touch with Matt Masson (https://twitter.com/mattmasson) for 'tis he that wrote it.

regards

Jamie

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Kirk Barnhart — Wed, 13 Mar 2013 19:58:47 GMT

Aaron Lowe. Would you be willing to share your custom build definitions for IS/RS/AS projects

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

Brian Reynolds — Thu, 04 Apr 2013 18:52:42 GMT

Jamie,

Great explanation. Would be helpful to talk about the database upgrade scenarios too.

Brian

re: Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

jamiet — Tue, 09 Apr 2013 10:36:38 GMT

Hi Brian,

In what context? The above should work for schema upgrades too - that's one of the benefits of the declarative development paradigm that SSDT extols.

regards

Jamie