Last week I came across this tweet that got me thinking about participating in the T-SQL Tuesday #86 SQL Server Bugs & Enhancement Requests event:

I have not participated in T-SQL Tuesday before and although I have thought about taking this moment to write about BIML (I am working towards a “past year with BIML” reflection post), I have instead decided to write about something else that has been bothering me for a while:

#759940 Why can’t I build an SSIS Project with MSBuild “out of the box”?

My background is in Software Engineering, so you could say I am an “accidental BI guy” and I am still shocked that some of the good Software Engineering practices do not seem to have landed in “the BI World”.

Being able to do a reproducable (ISPAC) build without additional tools should have been part of the SSIS development/MSBuild stack by now.

(on a side note, this same issue is also true for #759939 SSAS and #759938 SSRS)

Granted, there are some workarounds floating around (e.g. “SSISMSBuild”), but the SSDT (Database Project) people actually seem to get this right.

For comparison, let’s have a look at some of the practices one can apply when developing a C# Console Application:

1. The ‘result’ of development is an executable that you can run from a Command Prompt

2. The ‘input’ is a C# Console Project containing a .csproj file and some .cs files containing the actual code

3. The .csproj is actually an MSBuild xml file (which is comparable to a Makefile on unix systems) and this allows for build automation.

4. When developing this program one would typically store all source code and projects inside Version Control.

5. There might even be written tests that verify correctness of (parts) of the code.

6. Continuous Integration might be applied to ensure we have working result at any moment in time. This means that a build is done and tests are executed on the resulting output whenever somebody checks in a change.

Now compare this with SSIS Development:

1. The ‘result’ of development is an ‘ISPAC’ file you can deploy to a server. ISPAC or (Integration Services Project Deployment File Format) was introduced in SQLServer 2012 with the SSIS Project Deployment Model. Whereas previously each package was a single unit of deployment, with the Project Deployment Model everything (including packages and parameters) are combined into a single (ISPAC) file ready for deployment. Also the managing of configuration (e.g. for Production, Acceptance, Testing, Development) can be done in a much easier way by using Project Parameters and Environment variables.

2. The ‘input’ is an SSIS Project containing a .dtproj file and some .dtsx files containing the actual packages.

3. The .dtproj file is NOT an MSBuild based project. I don’t know for sure, but I think this is probably a remnant of some old (pre-MSBuild) build system. Since SSIS was introduced in SQLServer 2005 and MSBuild was introduced in 2005 with .NET 2.0 it might be that the SSIS development tools (and thus the project system it uses) was never build with the MSBuild system in mind. The only ‘official’ way to build an ISPAC is by using Visual Studio. As a result of this, one can not build an ISPAC file with MSBuild “out of the box” and thus there is no good and official way to automate building of ISPAC files.

4. There is nothing preventing you to use Version Control with SSIS development. But beware of merging .dtsx packages! You should probably treat it like a binary file and not like a text file.

5. Unfortunately out of scope for this post, but there are actually ways to automate testing of SSIS packages. We have a custom build SSIS testing framework at work (which is also a something I want to write about).

6. CI can also be applied for SSIS development (and this is something we actually do at work).

Hopefully, the (near) future will bring us more SQLServer MSbuild support in addition to what the SSDT (Database Project) Team is bringing.

In this post I am going to explain howto setup SyntaxHighlighter on a Jekyll blog (for the purpose of writing about BimlScript).

Warning

This is my first blog post after not having any real website/blog for a loooooong time. That means I am still figuring out several things:

• how I want my workflow to be
• how the formatting of the regular text should be
• how the example code should look on this blog
• how Jekyll works
• how this blog should look

As a result, your reading experience may vary and I am open to suggestions on how to improve things.

That being said, lets dive into how to setup Jekyll and SyntaxHighlighter for BimlScript.

Why use SyntaxHighlighter instead of Rouge?

I am hosting this blog on GitHub Pages and they have integrated Rouge as a syntax highlighter. As of this writing the Rouge author has not included my pull request for BimlScript yet. Next to that Github Pages do not allow user plugins, which means I currently cannot ‘solve’ this using the Rouge highlighting route and keeping my workflow the same.

Since I plan on writing about BimlScript in the (near) future I went looking for alternative solutions. Due to the nature of integrating with Jekyll I thought the most flexible route would probably be a javascript based highlighter since that would be easier to integrate. I looked at a number of different solutions and partly because the official BimlScript website actually uses SyntaxHighlighter, it makes sense to use it as well. Meeting Scott Currie at the Biml User Group Netherlands Kickoff also helped with making this decision.

It makes sense for the Biml community to be able to blog (with syntax highlighting) about Biml, as a result I am also considering to make an overview/instruction on how to use other solutions on different platforms but that is out of scope for this blog post. Please contact me or write a blog post about the (Biml) highlighting technology you are using.

Note though that SyntaxHighlighter version 4.0 is in progress on github. However, one of the things that has changed is the build proces and I have not gotten that working on Windows yet. So for the moment these instructions are based on the 3.0.x version.

How and where to download it

At the moment of writing SyntaxHighlighter 3.0.83 is the latest official release and you can download it here from the website.

SyntaxHighlighter can be extended with new syntaxes (like BimlScript) through brushes (plugins) and the BimlScript brush and dependency can be downloaded from BimlScript.com. You will need:

• shBrushBimlScript.js. (Note that this brush file also contains a slightly modified Xml brush, this is only relevant if you might also want to use xml highlighting)
• shThemeBimlScript.css

Preparing files for Jekyll

SyntaxHighlighter comes with a lot of brushes for different syntaxes and themes. Depending on your needs you might want to install/use more than what I will describe here. Depending on where you prefer to put certain filetypes in your Jekyll sources tree you might need to tweak the instructions a bit.

Download and extract the contents of the SyntaxHighlighter zipfile somewhere. The resulting directory structure should be:

syntaxhighlighter_3.0.83
    ├───compass
    ├───scripts
    ├───src
    ├───styles
    └───tests
        ├───brushes
        ├───cases
        └───js			

The things we need from the official release are located in the scripts and styles directories and are:

- scripts/shCore.js
- styles/shCore.css

Copy all relevant files to your Jekyll source tree. I am assuming the .js files end up in your js/ folder and .css files and up in your css/ folder.

The end result should be something like:

YourJekyllSources
    ├───js
    |   ├───shCore.js
    |   └───shBrushBimlScript.js
    └───css
        ├───shCore.css
        └───shThemeBimlScript.css

Integrating files into Jekyll

Now that we have all files stored in the Jekyll source tree, we need to modify some files in order to integrate it into the website generation and enable SyntaxHighlighter.

The following needs be put in the _includes/head.html:

    <link rel="stylesheet" href="/css/shCore.css">  
    <link rel="stylesheet" href="/css/shThemeBimlScript.css">

The following needs be put in the _includes/footer.html:

    <script type="text/javascript" src="/js/shCore.js"></script>
    <script type="text/javascript" src="/js/shBrushBimlScript.js"></script>
    <script type="text/javascript">
        SyntaxHighlighter.defaults['html-script'] = true;
        SyntaxHighlighter.all()
    </script>

Markdown syntax for SyntaxHighlighter

SyntaxHighlighter uses the <pre> tag with class brush: bimlScript to do syntax highlighting. In order to do this with Jekyll/Markdown, you need to use the following in your markdown files:

<pre>
    <!-- This is where you BimlScript content goes -->
<pre>
{: class="brush: bimlScript"}

Your first BimlScript blogpost

When you have everything setup correctly, then you should be able to do something like the below in a blog post:

    
 
    <#@ template language="C#" hostspecific="True"#>
    <#@ import namespace="Varigence.Languages.Biml.Connection" #>
    <#@ import namespace="Varigence.Hadron.Extensions" #>
    <#@ import namespace="Varigence.Hadron.Extensions.SchemaManagement" #>
    
    <#+ public ImportResults Results
        { 
            get
            {   
                return ((AstOleDbConnectionNode)RootNode.Connections["AdventureWorksLT"]).ImportDB();
            }
        }
    #> 
    
    
        
            <#=Results.SchemaNodes.GetBiml()#>
        
         
            <#=Results.TableNodes.GetBiml()#>
        
    

Next steps

That concludes the very first item on this blog.

I have some ideas about a couple of BimlScript posts and I am also interested in what kind of workflow other people are using when writing BimlScript related articles and blog posts.

Feel free to comment below or contact me directly.

Welcome to the (re)build of my blog in Jekyll.