DataDude Annoyance #13: Project variables omitted from output script.

DataDude Annoyance #13: Project variables omitted from output script.

If you're using any software product for a non-trivial purpose, you'll find that it has flaws that will (upon occasion) drive you up a wall. It doesn't make any difference what it is or who produced it. I've even heard Mac users, upon occasion, complain about bugs in their toys.

But sometimes these little annoyances make you ask questions like, "Who the &@#$ tested this thing?!" I've had more than my share of those this week. Don't get me started about WiX and the Windows Installer. Grrr.

Fortunately, I can do something relatively simple about this one with Visual Studio for Database Professionals SR1 (a.k.a. DataDude or DBPro).

"Feature": DataDude projects allow you to specify project-level variables in the project properties on the Variables "tab" (or "sheet" depending on who you ask what it's called) BUT those variables are not written into the project build output (a T-SQL script) as :setvar statements.

In case you wonder why this matters, it really sucks when you're including DDL produced by a DBPro project into an MSI to be deployed as part of a deployed "product" (like an ETL Framework, for example). If those variables are omitted, the DDL script bombs. Not good.

In this case (unlike my WiX blues), I'm not the only one who has complained. It's already logged as feedback @ connect.microsoft.com. If you think that it should be addressed, give it some love. Heh. Yes, people @ Microsoft actually look at stuff like that once in a while.

In the meantime... Enjoy my fix.

"Fix": Use the post-build event to write your own defined variables into the output script. Add a Post-Build.vbs script to your DataDude project that defines the additional variables and writes them into the output script.

Here's some VBScript to do it. (If I were really motivated, I'd read those variables from the project properties through VSTS automation or cheat and read the XML... but my wife is pestering me to go pack up the office to get ready for the move to Redmond.)

For the record, I *hate* VBScript, but sometimes it's the duct tape that binds the solution together... since my JScript syntax wasn't working (and it wasn't worth 5 minutes to figure it out, so I gave up and rolled back to VBScript)... and I was entirely too lazy to figure out how to make a PowerShell script work as a post-build event. Heh.

The NWCandence blog on Branching Strategies. MRod on tfs workspaces and reinstalling the OS or Team Explorer....

Patrick Neborg

7 Mar 2008 2:29 AM

When I tried this approach under TFSBuild, it locked up on me. Probably because that directory structures are completely under a TFSBuild scenario compared to IDE Build. So I adapted this solution slighty. I removed using a post build event to execute the vbs script. And in the TFSBuild.Proj file I added an after compile copy & exec task. Excerpt of the edit to TFSBuild.Proj shown here (not my solution is under \database\CMS\CMS below TFS build's solution root):

I also rewrote the vbs script for better performance. This version does not concatenate the .sql file into a string variable. Instead it temporarily renames the .sql file and creates a new empty .sql file. As it read line by line it also writes to the newly created empty .sql. At the correct spot it writes in the variable lines. And then finishes writing the remainder of the file. In the end it cleans up the .tmp file.