SQL Server Integration Services 2016 Control Flow Templates Introduction

By:   |   Comments (5)   |   Related: > SQL Server 2016


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.

Templates folder

When you right-click this folder, you have the option to create a new template, or add an existing one:

Add new template

When you add a new template, a file with the extension .dtsxt is added.

New template

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.

Template designer

You need to add at least one task to the template or it will raise an error:

One task minimum

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:

Fully defined template

As you can see, a data flow can be implemented in a template:

Data flow in 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.

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.

Inserted template

If you try to add a template file holding multiple tasks - which are not inside a single container - you get the following error:

Template error

Once a template has been added to a package, a .dtsx.designer file is added to the package.

Designer file

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.

Template connection manager

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.

Template configuration dialog

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.

Running a package with a template

The execution information of the tasks of the template are also included in the Progress tab of the package.

Progress tab

Note that it's not possible to debug a template on its own.

No debugging

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.

Deleting a task

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.

Updating template in package

After reloading, the inserted template will be updated.

Reloaded package

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, November 30, 2015 - 4:47:47 PM - Pavel Pawlowski Back To Top (40163)

Yep this was clear to me :-) but mentioned for others

Yes, I know where the source code is :) I was joking.

Anyway, the current implementation status looks to mi similar to the Database Connections in the Package Deployement Model

, when you have to open all the packages to re-apply chagnes to the connection defined in the SSIS Project. With Templates it is now similar.

Ans as we both have mentioned in the earlier comments, there is a lot of possible improvements especially related to the parameterization of the templates (variables, Parameters) etc. Let's hope it will further evolve prior RTM.

 

Monday, November 30, 2015 - 1:50:06 PM - Koen Verbeeck Back To Top (40162)

Yes, I know where the source code is :) I was joking.

I was implying that editing the source code only indicates that the whole template feature is currently "unifinished" and that there is lots of room for improvement.

Monday, November 30, 2015 - 11:40:14 AM - Pavel Pawlowski Back To Top (40161)

Hi,

the "go into the template source code" means, that you RightLick the template in the Solution Explorer and Choose "View Source Code".

This is also helpfull if you by mistake parameterize some component in the Template by using Package Paramter. This means, you choose some component in the Template, Right-Clik on it and Select Parameterize. Then if you choose Create new Package Parameter, then such parameter is created in the template. However when you use such template and you do not have the same parameter manually created in the package, such package will always fail with parameter not found.

And the problem is that as mentioned, there is no GUI in the template to be able to remove the parameter. The only way to remove the parameter is going to the source codde, locate the Parameters XML node and remove them.

Let's hope the Templates will be improved prior RTM of SQL Server. In case not, then it is the only suitable workaround for advanced Templates usage.

Monday, November 30, 2015 - 9:57:26 AM - Koen Verbeeck Back To Top (40158)

That's a great work around Pavel, but you lost me at "go into the template source code " ;)
Hacking your way into the templates is probably not what the SSIS team envisioned, and probably a few steps too far for the average users.

I still stand by my point that the templates are too inflexible in their current iteration, but I do hope this will change in future releases. It would be nice if parameters defined in the template would be automatically created in packages that import the template.

Thanks for sharing your work around (and I hope we won't need it in the future)!
 

Monday, November 30, 2015 - 8:25:32 AM - Pavel Pawlowski Back To Top (40157)

Currently you can use expressions and dynamic parameterization inside the templates by workaround.

  1. You Define set of variables/Package Parameters inside the Template (to be able to build the expressions)
  2. You setup the Expressions in the template using those variables and parameters
  3. You Remove the variables/Parameters (or rename them) in the Template - To remove parameter you have to go into the template source code as the tab for parameters is missing there).
  4. You Define the same varaibles and/or Parameters in the Package where the template is being used.
  5. As the template is part of the package and the varaibles/Parameters are aoutomatically visible in lower contexts, the template will use those winthout any issues.

In this way you are able to configure the template. The same is if you want to return some restul from the template. Simply utilize variables on the pacakge level. Only when developing the template, it is necessary to create those temporarily in the template as well.

Of course you have to remember that you have to delete or rename those variables in the template, otherwise during the execution they will take priority above those defined in the Package.














get free sql tips
agree to terms