TFS - SSIS

In my last blog post we built the process to build and deploy database models to each of our environments using ms-build.  https://sqljoel.wordpress.com/2017/06/30/tfs-team-foundation-server/

In my experience, SSIS hasn't been treated as cleanly in many organizations.  Many actually believe we can not use TFS to build and deploy or SSIS packages, but I am going to show you how.

One of the reasons we many believe SSIS can not be built and deployed with TFS is that we can not use the built in tools.  In order to build, we will use a command line build step and have to call the devenv.com, be sure visual studio is installed on your build box.  In this example for our arguments we will use our solution the /rebuild and /project switches.

Pop Quiz

Brent Ozar sent out a pop quiz which had me go through a range of emotions.

https://twitter.com/BrentO/status/872427294735958017

• Trust → Everyone knows Temp tables are stored in the TempDB.


• Surprise → Those Brackets.


• Fear → How can this work without those databases on my SQL SERVER?


• Anger → I should know this!


• Sadness → I have a lot to learn.


• Joy → I am learning something now.


• Disgust → If this is a quiz on my SQL Server, why would any of this work without those databases.

After going through all these emotions and reading some of the comments, I was finally at peace that the entire code block would execute.  If the Temp Tables are created in the TempDB no matter what database we are in, all the databases referenced should not have and impact, and we will only be looking at TempDB.