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:
- Create a package in BIDS
- Import the package as a template in MDDE Studio
- Set the properties you want to be configurable
- Create an instance of the template
- Set values for properties you want to change
- 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.
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
Click Connect and point it to your MDDE database.
I right click on Templates and select Import Template... to import the package I created in BIDS.
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.
Next we want to create an Instance of the template. Right click on Template Instances, and select "Create instance..."
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...
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.