Replicating the source system with Biml

Today we’ll take a look into how we can replicate the source system schema with Biml and SQL Server DDL statements.
No, this isn’t about replicating data as you tend to do with SQL Server. This is how to mirror the source system schema for scenarios when you’d like to have a staging area that is a 100% copy of the source system.

There are a couple of ways to do this and I’ll list 3 of them.

This example assumes you already have collections defined inside RootNode.Tables.
If not, you can use this SQL Server script to create a table containing most of the datatypes available for SQL Server.

 

1. BimlExtension Method: GetDropAndCreateDdl

This method allows you to output a predefined template DDL statement as a string.

Pros: The DDL template is predefined and you simple call “tableNode.GetDropAndCreateDdl()” to retrieve it.
Cons: Template is not easily manipulated.

Code nugget:

Preview window inside BimlStudio:

getdropcreateddl

 

2. User defined BimlScript:

This method allows you to output and control the DDL statement as a string. Alter as needed.

Pros: DDL template is user defined and it’s up to you how you’d like to output it.
Cons: Code nugget can get out of hand if you need to handle lots of arguments. Should be used in a CallBimlScript file.

Code nugget:

Preview window inside BimlStudio:

userdefinedgetdropcreateddl

 

3. User defined BimlScript:

This method adds “Annotations” when you import a schema using “GetDatabaseSchema” to your “Tables” collections.
The “Annotations” can be used the same way as the previous sample. Simply change the properties into the suitable Annotations tag.

Pros: Earliest definition of data types when imported with “GetDatabaseSchema” method. Other methods shown might contain data type conversions that aren’t what you’d like to be seen done.
Cons: Code nugget can get out of hand if you need to handle lots of arguments. Should be used in a CallBimlScript file.

Code nugget:

Preview window inside BimlStudio:

rawgetdatabaseschema

Leave a Reply

Your email address will not be published. Required fields are marked *