Biml Book Chapter 3, Unable to Generate SSIS Packages from BIML via BimlExpress 2018

Question

I am going through The BIML Book. I'm in chapter 3, page 90 (based on my PDF, purchase from APress).

I'm using SSDT/Visual Studio 2013 (Target SSIS Version: 2014) BIML Express 2018

I've got 3 BIML files that I've manually troubleshot:

1-2-CreateEnvironment.biml
1-2-CreateBimlTableObject.biml
x-2-CreateLoadPackage.biml

I've already done build 1, to generate (and subsequently executed) the DeployTable.dtsx file.

I'm trying to get all of the staging load packages described at the bottom of page 90, and in figure 3-34.

The issue is that the packages never get generated. They never show up in my solution.

The BIML Compiler output window shows:

Expanding Biml
Biml expansion completed.

There are no errors in VS. I've tried running VS as admin (thought maybe it possibly a permissions issue to write to disk) I've added an additional BIML file for logging the BIML Compiler details to a file.

That BIML is as follows:

<#@ template tier="1" #>
<#@ import namespace="Varigence.Utility.Logging" #>
<#
var loggingManager = new LoggingManager(Logging.LoggingMode.File) {
IsEnabled = true, LogFilePath = @"C:\temp\log.txt" };
LoggingManager.RegisterDefaultLoggingManager(loggingManager);
#>

Just wondering what I'm doing wrong, if anything. Do I need to use VS 2017?

Hi and thanks for buying the book. Nope, there is no requirement to use VS 2017 (I think we used VS 2013 but the code should run the same across all versions of Visual Studio). I'll have access in about 10 hours to my home computers but in the meantime, you can see if this article on debugging Biml can shed some light on where things are falling apart. Oh and make sure you're doing the multi-select as we did on the bottom of page 89 multi-select the files in Solution Explorer, right-click, and click Generate SSIS Packages — billinkc
Thanks billinkc. Will do. And yes, I've been doing the multi-select. Today, I installed VS 2017, added the BIMLExpress 2018 Extension and am still getting empty packages. I even found my old 2016 BIMLExpress extension installer this morning, and rolled back (or tried to) to this version in VS 2012,SSDT 2013, and '15. Still no change. What's weird is this was working in SSDT 2013 with the 2016 extension yesterday, which tells me something has changed. — klzbrt

klzbrt klzbrt · Accepted Answer · 2018-07-24T19:49:53

Finally! Starting to grok BIML and its tools. It's a different paradigm, IMO.

I was able to resolve the issue. It had to do with an invalid connection string parameter. Classic "Extra space/missing a space" issue.

The best thing from this, apart from getting it to work, was I was able to use Intellisense in the BIML file to emit results in the BIML Preview pane, showing that the collection of table nodes returned by Connection.GetDatabaseSchema() had a Count() of 0 (See line starting with <!--" below).

<#@ template tier="20" #>
    <#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
    <#@ code file="DebuggerUtilities.cs" #>
    <#
    var sourceConnection = RootNode.OleDbConnections["Source"];
    var includedSchemas = new List<string>{"HumanResources","Person","Production","Purchasing","Sales"};
    var importResult = sourceConnection.GetDatabaseSchema(includedSchemas, null,ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeColumnDefault |ImportOptions.ExcludeViews);
    #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <!--<#=importResult.TableNodes.Count()#>-->
        <# foreach (var table in importResult.TableNodes) { #>
        <Table Name="<#=table.Name#>" SchemaName="Staging.<#=table.Schema#>">
            <Columns>
                <# foreach (var column in table.Columns) { #>
                <#=column.GetBiml()#>
                <# } #>
                <Column Name="LoadDateTime" DataType="DateTime2"/>
            </Columns>
        </Table>
        <# } #>
    </Tables>
</Biml>

So the preview was emitting

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Tables>
    <!--0-->
  </Tables>
</Biml>

Which led me back to the Connection.

Biml Book Chapter 3, Unable to Generate SSIS Packages from BIML via BimlExpress 2018

1 Answers