Generating T-SQL to drop and create all indexes

In this second recipe, we will demonstrate another Biml metadata-based superpower. Sometimes you will need to drop all your table indexes and then recreate them. Instead of doing it through dynamic SQL, you can do it faster with Biml, because it gives you complete SQL statements. Let's generate SQL statements to drop and create indexes for each table.

Getting ready

Open Visual Studio 2019, and then open the ETLInAzure SSIS project.

Just think of a name for your second BimlScript file. Let's be original and call it Recipe2.Biml.

How to do it…

Let's add a new BimlScript file for this recipe.

1. Add a new BimlScript file to your solution. Rename it to Recipe2.biml. It should contain only opening and closing Biml tags.
2. Add the following code between Biml tags to configure a database connection and get the metadata of the database using the GetDatabaseSchema() method:

   <# var sourceConnection...