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.)

Dim fso, ts, line, x, y, script, variables, filepath

Const ForReading = 1, ForWriting = 2, ForAppending = 8

' NOTE: This name must match the DataDude "Build Output File Name" 

' in project properties.

filepath = "ETL Repository.sql"

' Append your additional variables here, one per line. 

' Include blank lines as desired for spacing.

variables = Array( _

     "" _

    ,"-- Additional variables added by Post-Build.vbs" _

    ,":setvar DatabaseMasterKeyPassword ""Change this password, please.""" _

    ,"" _

    ,"-->> Backup your Service Master Key!!!" _

    ,"-->> Backup your Database Master Key!!!" _

    ,"" _

    ,"-->> Be sure to test carefully!" _

    ,"" _

    )

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.OpenTextFile(filepath, ForReading)

x = 0

Do Until ts.AtEndOfStream

    line = ts.ReadLine

    x = x + 1

    If x = 7 Then ' Right after the last existing :setvar.

        ' Append variable definitions.

        For Each line In variables

            script = script & line & vbCrLf

        Next

    End If

    script = script & line & vbCrLf

Loop

ts.Close

Set ts = fso.OpenTextFile(filepath, ForWriting)

ts.Write(script)

ts.Close

Set ts = Nothing

Set fso = Nothing

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.