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.
[…] David Stein shows how you can use the GetQuerySchema() function in Biml to retrieve metadata for a query: […]
Hi David,
do you have an example of using GetQuerySchema against an ODBC-Database? Using the “normal” ODBC connection string I always get an error message stating that no OLEDB provider is given, which seems kinda weird to me, since I didn’t specify it as OLEDB anywhere:
An OLE DB Provider was not specified in the ConnectionString. An example would be, ‘Provider=SQLOLEDB;’.
I haven’t tried using GetQuerySchema against an ODBC connection. Is there some reason you don’t want to use OLE DB? If you are set on using an ODBC connection, you’ll need to use a proper connection string. You can find examples here: https://www.connectionstrings.com/sql-server/