Correct metadata is essential for every automation solution. When I started working with BIML I used one table that describes the source to target table mapping. (see the “Creating a Meta Data Driven SSIS Solution with BIML” series)
And that works fine in most simple scenarios like importing staging tables or when you are able to move business rules to a view. Key in these simple scenarios is that the columns in your target table have the same name and the same data type.
Extending the metadata model
In more recent assignments I added a column mapping table and used T-SQL to simplify the BIML script. With this approach I created more robust SSIS packages that provided extra possibilities, like:
- Incremental load patterns
- Converting data types
- Surrogate key lookups
- Expressions to calculate new values
A problem with this approach however is maintaining these column mappings. So I decided to take Visual Studio LightSwitch for a spin and create a simple application to fulfill my maintenance needs.
Visual Studio LightSwitch is a rapid application development (RAD) tool that is used to help write data-centric line of business (LOB) applications. An import prerequisite of LightSwitch is that you need to have a data model or construct one using the LigthSwitch GUI. Based on this data model you can easily define a set of screens and your application is done. So: No coding necessary. (although coding is possible to extend the standard functionality)
For my data model I decided to add two additional tables:
From a BIML perspective the ETLJob table is primarily used to create the master package with Execute Package and Execute SQL tasks. An ETLJob can be either:
- A generated package (with one or more table mappings)
- A manual package or a
- SQL Statement
So I ended up with the following model:
The SQL DDL Statements to create this model:
CREATE SCHEMA meta
CREATE TABLE meta.Connection
ConnectionID INT IDENTITY NOT NULL PRIMARY KEY
, ConnectionName VARCHAR(64) NOT NULL
, ConnectionType VARCHAR(20) NOT NULL
, ConnectionString VARCHAR(256) NOT NULL
, CreateInProject BIT NOT NULL DEFAULT(0)
, DelayValidation BIT NOT NULL DEFAULT(1)
CREATE TABLE meta.ETLJob
ETLJobID INT IDENTITY NOT NULL PRIMARY KEY
, ETLJobName VARCHAR(64) NOT NULL
, ETLJobType VARCHAR(20) NOT NULL
, ETLJobGroup VARCHAR(64)
, SQLConnection INT
, SQLStatement VARCHAR(512)
CREATE TABLE meta.TableMapping
TableMappingID INT IDENTITY NOT NULL PRIMARY KEY
, ETLJOB INT NOT NULL
, TableName VARCHAR(64)
, TableType VARCHAR(20)
, TableConnection INT NOT NULL
, TableSchema VARCHAR(64)
, SourceConnection INT
, SourceObject VARCHAR(256)
CREATE TABLE meta.ColumnMapping
ColumnMappingID INT IDENTITY NOT NULL PRIMARY KEY
, TableMapping INT NOT NULL
, ColumnName VARCHAR(64) NOT NULL
, ColumnType VARCHAR(20) NOT NULL
, ColumnDataType VARCHAR(20) NOT NULL
, SourceColumnName VARCHAR(64)
, SourceColumnDataType VARCHAR(20)
, Calculation VARCHAR(256)
, LookupConnection INT
, LookupObject VARCHAR(256)
, InputColumnName VARCHAR(64)
, InputColumnDataType VARCHAR(20)
, OutputColumnName VARCHAR(64)
Importing the data model in LightSwitch
To import this data model you only need 6 steps:
- Create a new database. (I named it MetaBase) and execute the afore mentioned DDL Statements to create the objects and relations.
- In Visual Studio create a New Project. Select LightSwitch as template and choose LightSwitch Application (Visual C#). Give the project a name (I used Metabase) and click OK.
- In the next screen click Attach to External Data Source.
- In the Attach Data Source Wizard form select Database and click next.
- In the Connection Properties window enter the server name\instance and Select the database you created in step 1. Click OK.
- Check Tables and use the default name in Specify the name of the data source in the Choose your Database Objects step. Click Finish.
You have imported the data model into LightSwitch.
Changing the data model
When you look at the LightSwitch designer in your data model you will notice LightSwitch made some small name changes, describing the relations. Do not try to correct these. Instead change the display name in the properties window. As I did for the source connection in this screenshot.
Creating the screens
Creating screens is even easier than creating the data model.
- Right-Click Screens in Solution Explorer and choose Add Screen ..
- In the Add New Screen dialog select Details Screen as template. Select MetaBaseData.ETLJob as Screen Data and check ETLJob TableMapping. Click OK.
- After I created the screen I made some small changes in the designer: Moved some fields up or down by dragging and dropping and changed the number of Lines in the SQL Statement field from 1 to 3.
I repeated steps 1 and 2 to create additional screens:
|Search Data Screen||Connections|
|Search Data Screen||ETLJobs|
|Search Data Screen||TableMappings|
|New Data Screen||Connections|
|New Data Screen||ETLJobs 2)|
|New Data Screen||TableMappings 1)|
|Details Screen||TableMappings 1)|
1) Add ColumnMappings under Additional data to include
2) Add TableMappings under Additional data to include
Navigation, Shell and Theme
Right-Click Screens en select Edit Screen navigation from the context menu. Change the screen navigation to the following image:
Choose General Properties and change the Shell to LightSwitch Standard Shell and the Theme to LightSwitch Blue Theme.
With Visual Studio LightSwitch you can easily create an application to maintain the meta data for your BIML solution. You create a normalized data model. Import it in LightSwitch and start adding screens. And if you like BIML you’ll probably like LightSwitch as well: Both make it easier to program your solution.
An additional advantage of storing the meta data in separate tables and creating an application to maintain that data is that you have a great overview of your data lineage and up to date documentation. Here are some screenshots of the application with data.