BIML, dynamically create Stored Procedures from Meta database.

In this blog, we are going to see how to create stored procedures dynamically using BIML, i would recommend readers to be familiar with my previous blog series before we deep dive.

In the previous blog, we have created couple of stored procedures manually which are used in SQL execution tasks. In this blog, we will be creating stored procedures on the fly at run time using BIML, this can be accomplished by calling a store procedure AutoGenerateStageProcedures from BIML ExecuteSQL task, the AutoGenerateStageProcedures procedure will take destinationSchemaName and destinationTableName as input parameters and use the parameters to query the Meta database –> SourceToTarget table. The query will extract all necessary data for the new procedure and assign the output to a string. Then the string will be executed which in-turn will create destination stored procedure at run-time.

The AutoGenerateStageProcedures procedure will create a new procedure for Dataflow OleDbSource task. The created procedure extracts the data from Stage table and pass it to DataVault tables.

The BIML compiler will throw error if any of the database definitions were not created during package creation. That is why, we are creating AdventureWorks.usp_Get_Address stored procedure while creating stage packages using BIML instead of creating it in Datavalue package creation.

To generate the stored procedure dynamically, we need to know how the procedure should look like and do we have all the data needed in Meta.SourceToTarget table. Once we have everything in SourceToTarget table, we can seamlessly generate stored procedures dynamically. Following is an example of stored procedure i want to generate the data i have in Meta.SourceToTarget table.

From the picture above, we have identified what columns and business keys the procedure should have and whether we have all the information in Meta.SourceToTarget table. Once we have everything in Meta database, the following code snippet is used to generate stored procedure dynamically.

Using this approach and proper Meta.SourceToTarget mapping, we can create however complex the procedure is in just a click. The only effort would be to write AutoGenerated stored procedure which should be once off activity.