Problem
At the time of writing, SQL Server 2016 preview (CTP 3.0) has been released. One of the exciting new features of SQL Server Integration Services (SSIS) is that you are able to define templates for the control flow. These templates can be added to regular SSIS packages to enable quick and efficient re-use. This tip will introduce you to this new feature.
Solution
Control flow templates allow you to create re-usable control flow tasks which you can easily embed in packages. However, the “template” name might be a bit confusing, as you cannot actually use them as a template. With a template, you would still be able to edit it after you inserted it in a package. The control flow templates on the other hand can only be inserted in a package, but not directly edited. It’s possible though to set certain properties of the inserted template. Maybe “package parts” would be a better name, a bit like report parts in Reporting Services.
SQL Server 2016 preview
As mentioned earlier, at the time of writing SQL Server 2016 is still in preview (at the time of writing CTP 3.0 has been released). This means that the features of SQL Server Integration Services can still change and that functionality might change, disappear or be added.
Defining Control Flow Templates
To test out this new functionality, you need to install the new preview of SQL Server Data Tools for Visual Studio 2015. This new preview also includes the BI projects for SSRS and SSAS. In the Solution Explorer a new folder has been added for the Control Flow Templates.
When you right-click this folder, you have the option to create a new template, or add an existing one:
When you add a new template, a file with the extension .dtsxt is added.
You can edit this file like a regular package, but the designer for a template has only two tabs: one for the control flow and one for the data flow.
You need to add at least one task to the template or it will raise an error:
On the other hand, a template can hold only one executable. If you need more tasks, you need to put them all inside one sequence container. In the CTP 3.0 preview, there are some limitations present. Some of them may or may not be removed in future versions.
- As in a regular package, you need to create connection managers. However, you cannot use project connection managers. Existing project connection managers do not show up when template is created and package connection manager inside a template cannot be converted to a project connection manager. Currently it’s possible to give a template connection manager the same name as a project connection manager, since they seem to be separated.
- Although it’s possible to add parameters to a template (by right-clicking a task and choosing Parameterize), actually using them will load to an error.
- SQL Server MVP Joost van Rossum noticed there’s an issue with script tasks and their connection managers. Check out his blog post.
- Since there’s only a control and data flow tab, you cannot add event handlers.
- Logging providers can be added to a template, but testing shows they do not actually do anything. They are not “copied” when a template is imported into a package.
Aside from these limitations, you can edit a template like any other package. It even can hold functional data flows. An example:
As you can see, a data flow can be implemented in a template:
Re-using the Control Flow Templates
Using a template in a package is straight forward. Once you save a .dtsxt template file, it is directly added to the bottom of the SSIS Toolbox.
You can simply drag a template from the toolbox to the control flow of a package, or you can double click on a template to insert it into a package. The control flow tasks will be added to the package as one single task. At the top right corner a T is shown to indicate that the task is actually a template.
If you try to add a template file holding multiple tasks – which are not inside a single container – you get the following error:
Once a template has been added to a package, a .dtsx.designer file is added to the package.
This designer file holds information regarding the templates used in the package. If all templates are removed from the package, the designer file will be automatically deleted as well. It’s important to mention that the designer file and the templates are a design-time only feature. Once you deploy a package to the server, the template information will be loaded inside the package. However, connection managers defined in the template have a (template) prefix in their name. The following screenshot is taken when configuring a package in the SSIS Catalog.
Be aware that when you rename a template, the designer file will not be automatically updated, so this will break packages using the template.
When using templates, there is (currently) not much flexibility. You cannot remove or add tasks to a template inside a package, you can only do this in the template itself. When you double click on a template in a package, the template configuration dialog opens up. In this dialog, you can set the different properties of the template, including the value of variables. You can also change the properties of the connection managers.
The configuration dialog allows for some flexibility, although for the moment it’s not possible to define expressions on a property; you can only hardcode values.
When you run a package with a template inside it, the tasks of the template will run as if they are part of the package. This means that you can see the runtime status of the different tasks.
The execution information of the tasks of the template are also included in the Progress tab of the package.
Note that it’s not possible to debug a template on its own.
Updating a Control Flow Template
When you update a template, its changes are propagated to all the packages using the template. Let’s delete on of the tasks of the template.
When you go back to the package, a pop-up notifies you that a template has changed. The package will be reloaded and you have the option to either save or discard changes of the package.
After reloading, the inserted template will be updated.
However, the package either needs to be already open, or it has to be opened manually in order to reload the templates! If not, the changes of the template will not be reflected in the package. This can be quite cumbersome if a lot of packages are using the template.
Conclusion
The new Control Flow templates are a great new addition to the Integration Services design environment. They will make re-use of common components a lot easier. However, in this first iteration there are some issues present:
- There is not much room for flexibility. You can only change properties through the Template Configuration Dialog, but currently expressions cannot be used. This also means that the templates are not actually “templates”: they are not a skeleton framework to get you started on a package. They are rather finished and fully functioning parts of a package which can be re-used. If you need true templating, read the tip Creating SSIS Package Templates for Reusability.
- You cannot debug templates.
- When you change a template, you need to re-open every package using the template.
Next Steps
- Try it out yourself! Download and install the SQL Server 2016 preview and create your own templates.
- For more info on Control Flow templates: Reuse Control Flow across Packages by Using Control Flow Templates.
- For more SQL Server 2016, read these other SQL Server 2016 Tips.
