BIML - 'AstTableNode' does not contain a definition for 'GetDropAndCreateDdl'

Question

I am working on a BIML project to generate SSIS packages. I have a separate static class for utility methods.

I am attempting to call GetDropAndCreateDdl() to get the DDL from the souce to dynamically create a table in the destination. This should work in theory as it is referenced in multiple posts: here and here as samples.

When generating the BIML, running the sample code below, I receive an error: Error: 'AstTableNode' does not contain a definition for 'GetDropAndCreateDdl' and no accessible extension method 'GetDropAndCreateDdl' accepting a first argument of type 'AstTableNode' could be found

public static string GetDropAndCreateDDL(string connectionStringSource, string sourceTableName)
{
    var sourceConnection = SchemaManager.CreateConnectionNode("Source", connectionStringSource);
    var sourceImportResults = sourceConnection.ImportTableNodes(Nomenclature.Schema(sourceTableName),Nomenclature.Table(sourceTableName));
    return sourceImportResults.TableNodes.ToList()[0].GetDropAndCreateDdl();

}

(Let's ignore the possibility of getting no table back or multiples for the sake of simplicity)

Looking at the varigence documentation, I don't see any reference to this method. This makes me think that there is a utility library that I am missing in my includes.

• using Varigence.Biml.Extensions;
• using Varigence.Biml.CoreLowerer.SchemaManagement;

What say you?

Joe

Answer 1

GetDropAndCreateDdl is an extension method in Varigence.Biml.Extensions.SchemaManagement.TableExtensions

ImportTableNodes returns an instance of Varigence.Biml.CoreLowerer.SchemaManagement.ImportResults and the TableNodes is an IEnumerable of AstTableNodes

So, nothing weird there (like the table nodes in the import results being a different type)

I am not running into an issue if I have the code in-line with BimlExpress.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
string connectionStringSource = @"Provider=SQLNCLI11;Data Source=localhost\dev2017;Integrated Security=SSPI;Initial Catalog=msdb";

var sourceConnection = SchemaManager.CreateConnectionNode("Source", connectionStringSource);
List<string> schemaList = new List<string>(){"dbo"};
var sourceImportResults = sourceConnection.ImportTableNodes("dbo", "");
WriteLine("<!-- {0} -->", sourceImportResults.TableNodes.Count());
//var sourceImportResults = sourceConnection.ImportTableNodes(schemaList,null);
var x = sourceImportResults.TableNodes.ToList()[0];
var ddl = x.GetDropAndCreateDdl();
WriteLine("<!-- {0} -->", sourceImportResults.TableNodes.FirstOrDefault().GetDropAndCreateDdl());

#>
</Biml>

The above code results in the following expanded Biml

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <!-- 221 -->
  <!-- IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[autoadmin_backup_configuration_summary]') AND type IN (N'V'))
DROP VIEW [dbo].[autoadmin_backup_configuration_summary]
GO

CREATE VIEW [dbo].[autoadmin_backup_configuration_summary]  AS
SELECT 
    ManagedBackupVersion,
    IsAlwaysOn,
    IsDropped,
    IsEnabled,
    RetentionPeriod,
    EncryptionAlgorithm,
    SchedulingOption,
    DayOfWeek,
    COUNT(*) AS DatabaseCount
FROM autoadmin_backup_configurations
GROUP BY
    ManagedBackupVersion,
    IsAlwaysOn,
    IsDropped,
    IsEnabled,
    RetentionPeriod,
    EncryptionAlgorithm,
    SchedulingOption,
    DayOfWeek

GO

 -->
</Biml>