How do I get the BIML compiler to allow c# 6 language features in Script Tasks, Script Components

Question

Desired result: Get my BimlStudio project to output an SSIS package whose script tasks (or script components) use C# 6 language features.

I've got an SSIS 2014 Project with a single package that I've imported into BimlStudio

Varigence BimlStudio 5.0 (64-bit)
Build 5.0.63501.0

Visual Studio Version info:

Microsoft Visual Studio Professional 2017 
Version 15.9.5
VisualStudio.15.Release/15.9.5+28307.280
Microsoft .NET Framework
Version 4.7.03056

Microsoft Visual Studio Tools for Applications 2017   00370-20004-06228-AA500

I'm to the point where I'm trying to compile my BimlStudio project. Not that I HAVE to, but because I'm trying to mirror the build behavior of my native SSIS 2014 project, I've change the default 64-bit MSBuild path in the .mst file from

C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe

to

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\amd64\MSBuild.exe

Technically, to get C# 6 features, I don't believe this is required, but hear me out.

In BimlStudio, in the Logical or Project View pane, I right-click my project, and click Build.

In the Output pane I get the following (note error in bold):

    ------ Build ------
    Working Directory: C:\PathToMyProject\BIMLStudioProject\output
    MsBuild Executable: C:\WINDOWS\system32\cmd.exe
    MsBuild Arguments: 

    To replicate this build with bimlc.exe. Use the bimlc.resp file:
    bimlc.exe @"C:\PathToMyProject\BIMLStudioProject\output\BIMLStudioProject.mst.bimlc.resp"

    Microsoft Windows [Version 10.0.17134.706]
    (c) 2018 Microsoft Corporation. All rights reserved.
    C:\PathToMyProject\BIMLStudioProject\output>C:\WINDOWS\system32\chcp.com 65001
    Active code page: 65001
    C:\PathToMyProject\BIMLStudioProject\output>C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\amd64\MSBuild.exe @"C:/_Development/Test Projects/BIML/BIMLStudioProject/output/BIMLStudioProject.mst.resp"
    'C:\Program' is not recognized as an internal or external command,
    operable program or batch file.
    C:\PathToMyProject\BIMLStudioProject\output>exit %ERRORLEVEL%
    Build Failed

So having spaces in the path to MSBuild is "verboten"?
I tried added double quotes around the path to the MSBuild exe.
That didn't work. Maybe there's another way to do this?
It's not my primary question but any help there would be appreciated.

So, side-stepping the BimlStudio IDE limitation, I jump over to the command line and execute the following:

    cd "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\amd64\"
    MSBuild.exe @"C:/PathToMyProject/BIMLStudioProject/output/BIMLStudioProject.mst.resp"

And this outputs the following (scroll to bottom to view bolded errors):

    Microsoft (R) Build Engine version 15.9.21+g9802d43bc3 for .NET Framework
    Copyright (C) Microsoft Corporation. All rights reserved.

    Build started 11/8/2019 4:12:07 PM.
    Project "C:\PathToMyProject\BIMLStudioProject\output\BIMLStudioProject.mst.ProjectView.bimlproj" on node 1 (default targets).
    BimlCompilerBuild:
    Message:: Biml Compiler Version 5.0.63501.0
    Message:: Options:
    $TargetBimlScriptPrecompiledAssemblyPackagePath =
    $PrecompileBimlScript = False
    $LicenseKey =
    $DisplayMachineCode = False
    $TransformationScriptSettings =
    $TransformationScriptResourceAssembly =
    $TransformationScriptBundle =
    $PackageConfigurationPath = C:\PackageConfigurations
    $DocumentationOutputPath = C:\PathToMyProject\BIMLStudioProject\documentation
    $DocumentationSettings =
    $DocumentationTemplate =
    $BuildDocumentation = False
    $CleanOutputFolder = True
    $WarnAsError = False
    $Warn = 4
    $TargetPath = C:\PathToMyProject\BIMLStudioProject\output\\
    $TempPath =
    $WorkflowPath =
    $BuildOnlyWithDependencies = False
    @BuildOnly:
    @SourceFiles:
    ...

C:\PathToMyProject\BIMLStudioProject\addedBiml\ScriptProjects\ST_4ed03a23e1db4022ad98d6615e195f26.biml
    ...
    @IncludeFiles:
    @TemplateFiles:
    @BimlDocSchemaGraphProfiles:
    @BundleFiles:
    @BundleSettings:
    @ResponseFiles:
    $SsisDeploymentModel = Project
    $SsasVersion = Ssas2014
    $SsasTabularVersion = SsasTabular2016
    $SsisVersion = Ssis2014
    $SqlVersion = SqlServer2014
    $DdlBuildMode = SsisPackages
    $SsisEncryptionPassword =
    $SsisExternalColumnsQueryTimeout =
    $ImportSsis = False
    $ImportSsisOptionsPath =
    $GetHelp = False
    Message:: Processing Biml Code...
    Message:: Running Project Transformers...
    Message:: Running Custom Transformers...
    Message:: Running Core Transformers...
    Message:: Emitting SQL Server Assets...
    Message:: 1/1 Emitting Project SagittaExtract.dtproj
    Message::     1/1 Emitting Package PackageTemplate
    In Script Project 'ST_4ed03a23e1db4022ad98d6615e195f26': \ScriptMain.cs(129,42): error : Error:: Unexpected character '$' [C:\PathToMyProject\BIMLStudioProject\output\BIMLStudioProject.mst.ProjectView.bimlproj]
    In Script Project 'ST_4ed03a23e1db4022ad98d6615e195f26': \ScriptMain.cs(223,35): error : Error:: Unexpected character '$' [C:\PathToMyProject\BIMLStudioProject\output\BIMLStudioProject.mst.ProjectView.bimlproj]
    Done Building Project "C:\PathToMyProject\BIMLStudioProject\output\BIMLStudioProject.mst.ProjectView.bimlproj" (default targets) -- FAILED.


    Build FAILED.

Notice that my script task has an invalid character '$'. This would correspond with doing string interpolation (C# 6.0 language feature).

So what do I do to get C# 6 language features in my script task?

I've tried using the Biml Project's default MSBuild path and version, and adding the /toolsversion switch.

MSBuild.exe ... /tv:14.0

Then, changed to use VS 2017's 64-bit MSBuild.exe

MSBuild.exe ... /tv:15.0

Neither worked.

Any help with getting this to work would be much appreciated.

Thanks.

score 2 · Accepted Answer · edited Dec 20 '19 at 19:58

Roslyn Not Supported in Biml Compiler

Unfortunately, the Biml compiler (bimlc) doesn't support Roslyn (I knew it was a future goal but there were dependencies and I don't know the current state beyond I heard a new drop was coming soon, likely to support the 2019 release of SQL Server).

I'll ping the folks at Varigence to confirm.

Spaces in path names

Have sucked in window s forever and will likely continue sucking forever. Yes, you can wrap with double quotes so "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\amd64\MSBuild.exe" will work but what if something else needs quotes or $Diety-help-you, you have to pass that as an argument to something like xp_cmdshell which only allows one item in the entire argument to have a pair of double quotes... Yeah, the approach is to use the dirty hacks of the dos 8.3 naming convention because they still work.

From a command line, Win+R cmd.exe

cd /d C:\
dir /x

That instructs the command shell to change drives and go to the root of the C drive The the /x in the second command

displays the short names generated for non-8dot3 file names.

So you should see some output like this

C:\>dir /x
 Volume in drive C has no label.
 Volume Serial Number is 3AB8-1C01

 Directory of C:\

10/30/2019  04:26 PM    <DIR>                       Dropbox
03/13/2019  06:07 PM    <DIR>                       Intel
08/25/2019  01:32 PM    <DIR>                       javajars
10/19/2019  03:33 PM    <DIR>          NEVERW~1     NeverwinterNights
03/12/2019  04:34 PM    <DIR>                       NVIDIA
08/25/2019  02:01 PM    <DIR>                       OnMyPath
03/18/2019  10:52 PM    <DIR>                       PerfLogs
10/14/2019  09:57 PM    <DIR>          PROGRA~1     Program Files
10/19/2019  03:51 PM    <DIR>          PROGRA~2     Program Files (x86)
11/14/2018  07:16 PM    <DIR>                       Python27
11/14/2018  07:16 PM    <DIR>          PYTHON~1     Python27amd64
06/22/2019  11:17 AM    <DIR>                       Src
06/30/2019  08:45 PM    <DIR>                       ssisdata
10/19/2019  01:52 PM    <DIR>                       TEMP
06/28/2019  02:21 PM    <DIR>                       tmp
07/30/2019  06:52 AM           110,312              UkLog.dat
05/28/2019  04:25 PM    <DIR>                       Users
10/19/2019  03:54 PM    <DIR>                       Windows
               1 File(s)        110,312 bytes
              17 Dir(s)  333,863,874,560 bytes free

NeverwinterNights and Python27amd64 are too long (more than 8 characters), that's why they show up in the first "name" column. "Program Files" and "Program Files (x86)" show up because they have space in their names (also, more than 8 characters).

What's critical is to use the alternate name. The pattern isn't hard, it's the first 6 character, less whitespace and then ~# from 1 to 9 UNTIL YOU GET MORE THAN 9 ENTRIES.

C:\PROGRA~2>dir /x mic*
 Volume in drive C has no label.
 Volume Serial Number is 3AB8-1C01

 Directory of C:\PROGRA~2

02/21/2018  06:00 AM    <DIR>          MICROS~2     Microsoft Analysis Services
03/16/2018  02:17 AM    <DIR>          MICROS~2.NET Microsoft ASP.NET
06/22/2019  01:24 PM    <DIR>          MI7DE1~1     Microsoft Azure Storage Explorer
04/24/2019  06:48 PM    <DIR>          MICROS~4     Microsoft Help Viewer
03/01/2018  12:36 PM    <DIR>          MIF5BA~1     Microsoft Office
09/12/2018  04:35 PM    <DIR>          MI29F1~1     Microsoft OneDrive
10/24/2018  06:12 AM    <DIR>          MICROS~3     Microsoft SDKs
01/19/2019  10:48 AM    <DIR>          MI9950~1     Microsoft Silverlight
07/11/2019  08:10 PM    <DIR>          MICROS~1     Microsoft SQL Server
04/24/2019  06:50 PM    <DIR>          MIEFB9~1     Microsoft SQL Server Management Studio 18
11/14/2018  07:17 PM    <DIR>          MIB055~1     Microsoft Visual Studio
02/21/2018  06:02 AM    <DIR>          MICROS~2.0   Microsoft Visual Studio 10.0
05/28/2019  04:33 PM    <DIR>          MICROS~3.0   Microsoft Visual Studio 11.0
05/28/2019  04:33 PM    <DIR>          MICROS~4.0   Microsoft Visual Studio 12.0
05/28/2019  04:33 PM    <DIR>          MICROS~1.0   Microsoft Visual Studio 14.0
10/24/2018  06:07 AM    <DIR>          MIFDD6~1     Microsoft Web Tools
05/28/2019  07:23 PM    <DIR>                       Microsoft.NET
               0 File(s)              0 bytes
              17 Dir(s)  333,865,095,168 bytes free

I'm sure the magic for how the alternate names work was documented ...25 years ago - I think this nonsense came out with Windows 95 but seriously, to heck with it.

So, you're looking for the build that is in the x86 folder. Do not let tab completion help you change directory as it will use the full name and not the short. so cd progra~2

Lather, rinse, repeat until you've gotten to the folder with your executable. Long file names won't matter so your Professional in the path won't break, it's only whitespace but eventually, you'll be in the folder with the .exe and then you'll just grab the path. I take the lazy route and just ask dir to build me the path with dir /s /b msbuild.exe

C:\PROGRA~2\MIB055~1\2017\Enterprise\MSBuild\15.0\Bin>dir /s /b MSBuild.exe
C:\PROGRA~2\MIB055~1\2017\Enterprise\MSBuild\15.0\Bin\MSBuild.exe
C:\PROGRA~2\MIB055~1\2017\Enterprise\MSBuild\15.0\Bin\amd64\MSBuild.exe

At this point, you should be able to pipe in either of those paths to MSBuild in BimlStudio which then saves them into the .resp file and see if that resolves the space in the path issue.

Unlocking C# 6.0 features

Today I learned you can use 6.0 features in SSIS Script Tasks and Components.

    public void Main()
    {
        string name = "mark";
        string v = $"Hello, {name}!";
        bool fireagain = false;
        Dts.Events.FireInformation(0, "Really worked", v, string.Empty, 0, ref fireagain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

and there in my Output window

Information: 0x0 at Script Task, Really worked: Hello, mark!

Note: Attempts to add Roslyn features to Script Tasks can effectively disable Script Task debugging

Please beware that using roslyn features can effectively disable Script Task debugging (see [this question thread](https://stackoverflow.com/questions/40677400/vs-2015-ssis-script-tasks-cannot-be-debugged)). In my case it was $ with strings, script run fine but debugging did not fire. — Ferdipux, Nov 11 '19 at 06:14

How do I get the BIML compiler to allow c# 6 language features in Script Tasks, Script Components

1 Answers1

Roslyn Not Supported in Biml Compiler

Spaces in path names

Unlocking C# 6.0 features