I have created the metadata tables using SQL server which also includes the flag for tables which needs to be transferred to the data warehouse. The pipeline using BIML seems to work. I would like to know is it possible to generate create/alter scripts using the metadata tables.
Steps I would like to work into the code.
1. Lets say 20 csv's are getting processed daily using the metadata tables.
2. A new CSV file gets added to the batch and/or existing table gets a new column or drops a column.
I would like to add/remove the info (columns, data types, and primary keys) based on the changes.
Once the info is added the DDL scripts are generated (could be create/alter) and executes on the database.
BIML's getTableSQL() drops and creates the table which does not work for existing tables.