Skip to main content

Import Biml Metadata with GetQuerySchema

Why Import Metadata From a Query​

GetDatabaseSchema is the workhorse for pulling schemas, tables, and views into Biml, but plenty of source data lives behind a query rather than a base table. GetQuerySchema fills that gap. It returns column metadata for any SELECT, view, table valued function, or stored procedure result, and it works with the same connection types that GetDatabaseSchema accepts. The method shipped with the 2018 editions of BimlStudio and BimlExpress.

Parameters​

GetQuerySchema accepts four arguments:

  • 'connection' is the source connection node. OLE DB, ODBC, ADO.NET, Oracle, Teradata, and Parallel Data Warehouse are all supported.
  • 'commandText' is the query, view name, function call, or procedure invocation that produces the result set whose schema is needed.
  • 'parameters' maps named query parameters onto SSIS variable values. Passing 'null' is common; query parameters can be embedded directly inside 'commandText' instead.
  • 'timeOut' is the cancel threshold in seconds. Zero means no limit.

Importing Schema From a SELECT Query​

The following script connects to a sample sales database and asks for the column metadata of a SELECT against an order table:

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

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

<#=importResult.GetBiml()#>

The result is a Biml table fragment with one Column element per projected column, including data type, length, precision, and scale where the provider can supply them:

<Table Name="" SchemaName="">
  <Columns>
    <Column Name="SalesOrderID" Precision="0" Scale="0" />
    <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Precision="0" Scale="0" />
    <Column Name="StaffID" Precision="0" Scale="0" />
    <Column Name="OrderDate" DataType="DateTime" Precision="0" Scale="0" />
    <Column Name="DueDate" DataType="DateTime" Precision="0" Scale="0" />
    <Column Name="ShippedDate" DataType="DateTime" Precision="0" Scale="0" />
    <Column Name="CarrierID" 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>

Other Supported Command Shapes​

The same method covers views, table valued functions, and stored procedures. Pass any of the following as 'commandText':

// View
var qry = "SELECT SalesOrderID, OrderDate, CustomerName, ExtendedPrice FROM dbo.InvoiceList;";

// Table valued function
var qry = "SELECT StaffID, ShippedDate, SalesOrderID, SaleAmount FROM dbo.tvf_StaffSales(4)";

// Stored procedure with a parameter embedded in the command text
var qry = "EXECUTE [dbo].[uspOrderDetail] @SalesOrderID = 11077";
var qry = "EXECUTE [dbo].[uspOrderDetail] 11077";

// Composite query with joins and a WHERE clause
var qry = @"SELECT OD.SalesOrderID, OD.ProductID,
  (CONVERT(MONEY, (OD.UnitPrice * Quantity * (1 - Discount) / 100)) * 100) AS ExtendedPrice
  FROM dbo.Product P
  JOIN dbo.[OrderDetail] OD ON P.ProductID = OD.ProductID
  WHERE P.Discontinued = 0;";

Limitations​

A few attributes are not returned because the result set is not a base table:

  • Table name and schema name are blank in the emitted Table element.
  • Column attributes such as nullability and computed flags are not always returned.
  • Primary key and unique key membership is not reported.

Wrapping Up​

GetQuerySchema turns any query into a Biml table fragment that can feed downstream package generation, AST manipulation, or scripted DDL. It is especially useful for source systems where the canonical input is a stored procedure or a curated view rather than a base table.