Helpful information and examples on how to use SQL Server Integration Services.
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:
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:
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:
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.
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.
Can the tool do this? Can I run dynamically created packages in sequential and in parallel?
Good job folks... :)
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.
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?
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.