Reed Me

Development schtuff, SQL Server & other random geekness. Now with more [fill in the blank]!

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

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

  • Comments 3

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.

image

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.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
  • The NWCandence blog on Branching Strategies. MRod on tfs workspaces and reinstalling the OS or Team Explorer....

  • 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):

    ....

    <Target Name="AfterCompile">

     <Copy SourceFiles="$(SolutionRoot)\Database\CMS\CMS\post_build.vbs" DestinationFolder="$(OutDir)" />

     <Exec WorkingDirectory="$(OutDir)" Command="&quot;$(OutDir)post_build.vbs&quot;" />

     </Target>

    </Project>

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

    On Error Resume Next

    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 = "CMS.(local).CMS.sql"

    filepath_Orig = filepath + ".tmp"

    ' 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 STR_DB ""STR""")

    Set fso = CreateObject("Scripting.FileSystemObject")

    'rename file with .tmp

    if fso.FileExists(filepath_Orig) then

      fso.DeleteFile filepath_Orig,True

    end if

    if fso.FileExists(filepath) then

           fso.MoveFile filepath , filepath_Orig

           fso.CreateTextFile filepath,true

           'read one file and write to the other file

           Set ts = fso.OpenTextFile(filepath_Orig, ForReading)

           Set ts2 = fso.OpenTextFile(filepath, ForWriting)

           x = 0

           Do Until ts.AtEndOfStream

               line = ts.ReadLine

               ts2.writeline(line)

               x = x + 1

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

                   ' Append variable definitions.

                   For Each line In variables

                       ts2.writeline(line & vbCrLf)

                   Next

               End If

               'script = script & line & vbCrLf

           Loop

           ts.Close

           ts2.Close

           'Set ts = fso.OpenTextFile(filepath, ForWriting)

           'ts.Write(script)

           'ts.Close

           Set ts = Nothing

           Set ts2 = Nothing

           fso.DeleteFile filepath_Orig

    else

    fso.CreateTextFile "post_build_output.text"

    Set ts2 = fso.OpenTextFile("post_build_output.text", ForWriting)

    ts2.writeline ("Could not find " + filepath)

    end if

    Set fso = Nothing

Page 1 of 1 (3 items)