With the announcement of SQL Server 2016 CTP3 and the available download, the first things I wanted to try are – as you can imagine – SSIS related things.
On of the most promising new features are Control Flow templates. In this blog post I will share my first learnings from using templates in SSIS. You as a a SSIS developer surely encountered the fact that you want to reuse several pieces of control flow logic again and again. Until now, reusability SSIS logic was somehow limited to the copy-past approached which definitely worked. But it only worked until you wanted to change the logic of the copied actions. And then the nightmare of refactoring began: searching for all instances of that single piece of duplicated code and then – changing the logic to the new desired state! With the concept of SSIS Control Flow Templates the plan is to make life easier: Control Flow Templates provide the possibility to pre-develop pieces of SSIS control flow logic that can be reused in several occurrences in one or multiple SSIS packages.
If you want to have a look at SSIS control flow templates, you need two things – SQL Server 2016 for running those packages and the appropriate development environment (SSDT October 2015 Preview – see my blog post for more details)
After creating a new SSIS project from scratch the new dev-environment and project structure looks like the following:
There are two points I would like to mention:
- In the SSIS toolbox, there is a subcategory named “Control Flow Templates” which is grayed out after creating a new SSIS project.
- In the Solution explorer you can spot a new tree-node likewise named “Control Flow Templates” – which is empty in a new SSIS project.
Creating a SSIS Control Flow Template
With a right-click and opening the context menu there are three options provided in order to add a template to the current project:
After creating a new template the designer window for this template is opened. As you can see in the screenshot – the template file itself gets a file extension of *.dtsxt.
As a first step I want to fill the template with life – for this demo work, I am adding two SQL tasks onto the template designer area (I also added a connection manager to the template). What I found out is that this does not work if you do not combine/group multiple entries into a single container. The SSIS designer raises the following error if there are multiple top-level components in a template file:
That error is resolved with the usage of a single sequence container as top-level object.
In the following I will use some object-oriented naming conventions – I will refer the template itself as template class and the template object that is used in a SSIS package I will call a template instance.
Use a SSIS Control Flow Template
A SSIS Control Flow template can be used in a simple SSIS package. There are several ways to integrate an existing template (template class) to your SSIS Package. If a SSIS package is opened in the designer and there are templates in the current project defined, the SSIS toolbox and the above mentioned sub-category “Control Flow Templates” gets into action. All templates from the current project are now displayed in the SSIS toolbox.
To use Template1 – simply drag and drop it onto the designer surface of the package. The sequence container with two SQL tasks (template instance) now appears in the designer. Template instances are decorated with a small “T” in the upper right corner.
The template instance itself is a read-only thing – you can position it wherever you want, you can integrate it in the overall control flow but you cannot change the inner components neither their relationships or ordering. You even cannot disable pieces of the template object. Either a full disable or nothing else! The top-level sequence-container of the template object can be renamed (to get a some more meaningful naming).
Updating a template (plus refreshing template instances)
The next testing step is the update mechanism of templates. The big question is: Will changes to a template class be propagated to template instance (SSIS package)? In order to test this, I made a change to the template class (a third SQL task is added).
The template class is saved and after changing to the already open SSIS package SSDT shows a dialog mentioning changes in the template class and asking the user whether those changes should be reflected in the package. And that is exactly the action I would like to see here – semi-automatic updates of related SSIS packages.
When the SSIS package opens again the new and updated template instance is displayed. As you can see in the screenshot below there are some minor displaying issues (but remember – the designer version I used here is a preview version. This hopefully will be fixed in a future version). But there is a solution to this issue – just resize the sequence container and everything is fine again.
From a first point of view – the template concept looks promising. There are many open questions (like connection managers, configurations, referencing and refreshing to name some of them) which I hopefully will answer in some following blog posts. Stay tuned and in the meantime –happy CTP-testing!