MDDE - MetaData Driven ETL

MDDE - MetaData Driven ETL

Rate This
  • Comments 4

If you haven't read about MDDE yet, be sure to read the blurb on the MDDE Codeplex homepage. There's currently not much documentation about it, but I plan on sharing things about it as I start to learn how to use it myself. 

The MDDE process:

  1. Create a package in BIDS
  2. Import the package as a template in MDDE Studio
  3. Set the properties you want to be configurable
  4. Create an instance of the template
  5. Set values for properties you want to change
  6. Generate packages for your instance

MDDE requires a repository (database) to store all of its information. The source on codeplex contains a Database Project (called Schema) which can be used to create everything you need.

After the repository is setup, we'll need to create a package to use as our template. Because of the processing it does, MDDE requires explicit support for package elements, and will throw an exception if your template contains something it doesn't know about. The following package elements are currently supported:

  • Containers
    • Sequence
    • For Each
    • For
  • Connection managers
  • Event handlers
  • Log providers
  • Variables
  • Execute SQL Task
  • Data Flow Task
    • Derived Column
    • Lookup
    • OLEDB Command
    • Row Count
    • Merge
    • Sources
      • Flat File
      • OLEDB
    • Destinations
      • Flat File
      • Raw File

One of the enhancements I'm hoping to see is the ability to pass through anything it doesn't have code for already.

As a starting example, I'll create a template from a very simple package that contains a single Task.

image

Everything in MDDE is done through the main executable - Microsoft.SharedSource.SqlServer.MDDE.ManagementStudio.exe

Launching it brings up a UI. Click Connect and point it to your MDDE database

image

Click Connect and point it to your MDDE database.

MDDE Studio

I right click on Templates and select Import Template... to import the package I created in BIDS.

image

In the Template Components window, I can see a break down of the package. By selecting the Execute SQL Task under Executables, I can see all of the properties that MDDE has exposed. To make a property configurable for your template, check the Is Configurable box next to the property name.

In this example, I'm going to make the following things configurable:

  • Execute SQL Task - SqlStatementSource - the SQL statement that will be executed by the task
  • LocalHost.AdventureWorks - ConnectionString - The connection manager used by the Execute SQL Task

After importing the template, it shows up under the Templates node.

image

Next we want to create an Instance of the template. Right click on Template Instances, and select "Create instance..."

image 

From the Create Template Instance page, we set values for the properties we flagged as configurable in the last step. I give the instance a name, and slightly modify my SQL statement just to have something modified.

After creating an instance, I can now generate packages based on it. Right click on the Template name (in my example, ExecuteSqlTemplate), and select Generate packages...

image

Select the instance you just created, and provide a path to save the package (file system only right now). After clicking the Generate Package button, you'll have an instance of your package template with all of the values filled in.

Simple, right? My example is also not very useful - this is already something you can do using package configurations. If you go back and look at the Create Template Instance dialog, you'll notice two other tabs on the right - Mappings and Collections. These are where you'd configure columns for a data flow, and is where MDDE gets really interesting. I'll show these off in my next example.

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post
  • Sounds good. But, tell me the pros/cons of this new born baby. What are the features? How stable is it on 64bit environments? What type tasks should I create using this tool?

    As a first step I am thinking a basic dataflow task.

    -Run a spc from DatabaseA.

    -Truncate TableB at DatabaseB.

    -Push data to TableB.

    -Log errors if any, log number of records moved.

    -Rebuild indexes.

    Can the tool do this? Can I run dynamically created packages in sequential and in parallel?

    Good job folks... :)

    Thanks,

    Cem

  • Hello Cem!

    These are great questions! You might want to post then on the MDDE codeplex project page, though. I haven't had time to dig any deeper into it to get a better understanding of how the data flow portions work.

  • Hi,

    I have one more question. Does this app create SSIS packages on the fly, at execution time based on meta data, or static, at design time?

    Thanks,

    Cem

  • Static package generation. You use the tool to load a template, and generate packages off of it.

    Of course, the code is available... with minor modifications, it could be turned into a dynamic engine.

Page 1 of 1 (4 items)