Import Biml Metadata with GetQuerySchema

This post is part of a series, Short Cuts to Biml Mastery which shows you how to learn BimlScript through a series of short cuts and remain productive while doing so.

The GetQuerySchema method provides an excellent way to import metadata from a query, in a similar way as GetDatabaseSchema imports schemas, tables, and views. GetQuerySchema was introduced last year with the 2018 editions of BimlStudio and BimlExpress.

Parameters

GetQuerySchema requires the following parameters:

Parameter Name	Description
connection	All major connection types supported including: OLE DB, ODBC, ADO.NET, Oracle, Teradata, and Parallel Data Warehouse.
commandText	Query for metadata import. See examples below.
parameters	Provides the capability to map a named parameter onto a SSIS package variable value. I've always passed in null because the format is complicated so I include query parameters with commandText.
timeOut	How many seconds to wait before canceling the query. Zero means wait forever.

Examples

As with all of the articles in this series, the following code runs against the Northwind sample database.

<#
  var srcConn = SchemaManager.CreateConnectionNode("connNorthwind", @"Provider=SQLNCLI11;data source=localhost;integrated security=SSPI;initial catalog=Northwind;");
  var qry = "SELECT * FROM dbo.Orders";

  var importResult = ExternalDataAccess.GetQuerySchema(srcConn, qry,null,0);
#>

<#=importResult.GetBiml()#>

And this results in the following Biml.

<Table Name="" SchemaName="">
	<Columns>
		<Column Name="OrderID" Precision="0" Scale="0" />
		<Column Name="CustomerID" DataType="StringFixedLength" Length="5" Precision="0" Scale="0" />
		<Column Name="EmployeeID" Precision="0" Scale="0" />
		<Column Name="OrderDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="RequiredDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="ShippedDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="ShipVia" Precision="0" Scale="0" />
		<Column Name="Freight" DataType="Currency" Precision="0" Scale="0" />
		<Column Name="ShipName" DataType="String" Length="40" Precision="0" Scale="0" />
		<Column Name="ShipAddress" DataType="String" Length="60" Precision="0" Scale="0" />
		<Column Name="ShipCity" DataType="String" Length="15" Precision="0" Scale="0" />
		<Column Name="ShipRegion" DataType="String" Length="15" Precision="0" Scale="0" />
		<Column Name="ShipPostalCode" DataType="String" Length="10" Precision="0" Scale="0" />
		<Column Name="ShipCountry" DataType="String" Length="15" Precision="0" Scale="0" />
	</Columns>
</Table>

This method also works with the following:

• Complicated Select queries with joins, where clause, etc.
• Views
• Table Valued Functions (TBF)
• Stored Procedures

// View
var qry = "SELECT OrderID, OrderDate, CustomerName, ExtendedPrice FROM dbo.Invoices;";

// TBF (Added to Northwind by Me)
var qry = "SELECT EmployeeID, ShippedDate, OrderID, SaleAmount FROM dbo.tbf_EmployeeSales(4)";

// Stored Procedure - Notice the parameter included with commandText. 
var qry = "EXECUTE [dbo].[CustOrdersDetail] @OrderID = 11077";
var qry = "EXECUTE [dbo].[CustOrdersDetail] 11077";

// Complicated query with Join and Where clause	
var qry = "SELECT OD.OrderID, OD.ProductID, (CONVERT(MONEY, (OD.UnitPrice * Quantity *(1-Discount)/ 100))* 100) AS ExtendedPrice FROM dbo.Products P JOIN dbo.[Order Details] OD ON P.ProductID=OD.ProductID WHERE P.Discontinued=0;";

Limitations

There are a few minor limitations:

• No table or schema names are imported for obvious reasons.
• Some column metadata attributes like nullability or computed aren’t returned.
• Doesn’t return whether a column is part of a primary or unique key.

Next Steps

This post explains the usage of GetQuerySchema for importing query metadata into Biml. It’s very easy to use and works with almost any type of query. In the next article, we’ll learn how to insert C# code nuggets into existing Biml code.