Generating T-SQL select statements for all tables

Let's start with the most straightforward and demonstrative example – generating a T-SQL statement. Biml has access to a treasure trove of metadata, or database information, such as table names, schema names, column names, foreign keys, indexes, and more. Having this valuable data, we can do lots and lots of useful things! We will see how we can generate a list of select statements for every table in a database that counts the number of rows of each table.

Getting ready

Open Visual Studio 2019, and then open the ETLInAzure SSIS project that was created in the Installing prerequisites section.

How to do it…

Add a new BimlScript file and, using the power of Biml native objects, generate your T-SQL code:

1. To add a new BimlScript file to your solution, right-click on the SSIS project name and choose Add New Biml File as shown in the following screenshot:

   Figure 10.3 – A screenshot showing adding...